SQL Query to group text based on numeric column

Question

I have a table 'TEST' as shown below

Number | Seq   | Name
-------+-------+------
123    |  1    | Hello
123    |  2    | Hi
123    |  3    | Greetings
234    |  1    | Goodbye
234    |  2    | Bye

I want to write a query, to group the table by 'Number', and select the rows with the maximum sequence number (MAX(Seq)). The output of the query would be

Number | Seq   | Name
-------+-------+------
123    |  3    | Greetings
234    |  2    | Bye

How do I go about this?

EDIT: TEST is actually a table that is the result from a long query (joining multiple tables) that I have already written. I already have a (SELECT ...) statement to get the values I need. Is there a way to remove duplicate rows (with the same 'Number' as shown above) and select only the one with maximum 'Seq' value. I am on Microsoft SQL Server 2008 (SP2)

I was hoping there would be a way to achieve this by

SELECT * FROM (SELECT ...) TEST <condition to group>

Can you please share your query and the part yu are stuck in? — z atef, Sep 22 '16 at 19:28
What should happen where there are ties (as obviously there are in your sample inputs)? Pick one at random? This seems to be what happened in your sample output (or perhaps you chose by alphabetical order?) — mathguy, Sep 22 '16 at 19:33
@mathguy I have edited the question. That scenario wouldn't happen. — woodhead92, Sep 22 '16 at 19:59
Err...that is a much different question, with a different answer. Pretty unfair to the folks who already answered your old question. — mathguy, Sep 22 '16 at 20:09
It would help to state your Oracle version as well; Oracle 12 may have some direct ways to achieve your goal. — mathguy, Sep 22 '16 at 20:13

score 1 · Answer 1 · answered Sep 22 '16 at 19:29

1

You can use a select win in clause

select * from test 
where (number, count) in (select number, max(count) from test group by Number)

answered Sep 22 '16 at 19:29

ScaisEdge

129,293
10
87
97

I have edited my question to suit my current scenario. I tried this out by running "SELECT * FROM (SELECT...) TEST WHERE Seq in (SELECT MAX(Seq) FROM TEST GROUP BY Number)" and faced a ' Invalid object name 'TEST'.' error. – woodhead92 Sep 22 '16 at 19:58

score 1 · Answer 2 · answered Sep 22 '16 at 20:05

SELECT *
  FROM (SELECT test.*, MAX (seq) OVER (PARTITION BY num) max_seq
          FROM test)
 WHERE seq = max_seq

I changed the column name from number because you can't use a reserved word for a column name. This is pretty much the same as the other answers, except that it explicitly gets the maximum sequence number for each NUM.

score 0 · Answer 3 · answered Sep 22 '16 at 19:31

Another option is to use a windowed ROW_NUMBER() function with a partition on the number:

With Cte As 
(
    Select  *,
            Row_Number() Over (Partition By Number Order By Count Desc) RN
    From    TEST
)
Select  Number, Count, Name
From    Cte
Where   RN = 1

score 0 · Answer 4 · edited Sep 22 '16 at 21:45

0

You want to use an ANALYTIC function together with a conditional clause to get you only the rows of TEST that you desire.

WITH TEST as (
     ...your really complex query that generates TEST...
)
SELECT
   Number, Seq, Name, 
   RANK() OVER (PARTITION By Number ORDER BY Seq DESC) AS aRank
FROM Test
WHERE aRank = 1
;

This returns the Number, Seq, Name for each Number grouping where the Seq is maximum. Yes, it also returns a column named aRank with all '1' in it...hopefully it can be ignored.

edited Sep 22 '16 at 21:45

a_horse_with_no_name

497,550
91
775
843

answered Sep 22 '16 at 20:30

JasonInVegas

380
3
9

Getting the following error - 'SQL Server Database Error: Invalid column name 'aRank'.' – woodhead92 Sep 22 '16 at 20:35
Whoops, didn't notice the sql-server tag...adjusting SQL syntax....try including the AS syntax as shown now. – JasonInVegas Sep 22 '16 at 21:22
Implemented the self join solution listed in http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column and it did the trick! I have listed that solution in the comments too. – woodhead92 Sep 23 '16 at 21:57

score -1 · Accepted Answer · edited May 23 '17 at 12:06

-1

The solution to this is to do an self join on only the MAX(Seq) values. This answer can be found at SQL Select only rows with Max Value on a Column

edited May 23 '17 at 12:06

Community

1
1

answered Sep 22 '16 at 20:45

woodhead92

117
1
12

SQL Query to group text based on numeric column

5 Answers5