2

I have a result from the SQL query, which is displayed below,

enter image description here

I want to build a SQL query, which can return the row includes maximum number from the last column if any two rows (or more than 2) have the same number from the first column.

For instance, from the table, you can see the top two rows have the same number from the first column, which is 2195333. If the SQL query runs, it will return the first row and the rest of rows, discarding the 2nd row only, since the last column for the 2nd row is 1, which is smaller than 2 from the 1st row.

I was thinking about using the while loop in SQL, like run the loop from the 1st row to the last row, if there are any rows have the same value from the first column, it will return the row which has the maximum value from the last column. Since I am new to SQL, I have no idea how to implement it. Please me help me. Thanks

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Akira
  • 273
  • 5
  • 15
  • `GROUP BY` on the column(s) you want distinct rows for, and use aggregate functions for the other values `MAX(other_column) AS other_column` – WillFM Jan 04 '18 at 00:34
  • @WillFM, I cannot groupby on the first column only, I need to groupby all other columns, so I can use Max function for the last column. – Akira Jan 04 '18 at 00:50

1 Answers1

4

The question, sample data, and desired results are lacking a bit.

But if I understand your question, you can use the WITH TIES clause in concert with Row_Number()

Example

Select Top 1 with ties *
 From  YourTable
 Order By Row_Number() over (Partition By YourCol1 Order By YourLastCol Desc)

Edit Use Dense_Rank() if you want to see ties

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • When I modify my code based on your solution, I got a error, which is `Incorrect syntax near 'Partion'.` – Akira Jan 04 '18 at 01:20
  • @AkiraKaneshiro Sorry typo Partition – John Cappelletti Jan 04 '18 at 01:21
  • @AkiraKaneshiro Happy it helped – John Cappelletti Jan 04 '18 at 01:36
  • @JohnCappelletti Cheers :-) – Ilyes Jan 04 '18 at 01:41
  • @Sami Thanks for the grin. – John Cappelletti Jan 04 '18 at 01:43
  • @John could you explain more how it works. Thanks :) I have another SQL problem, which is harder than this, could you help me out? Thanks in advance – Akira Jan 04 '18 at 01:44
  • 2
    @AkiraKaneshiro Row_Number() is one of the window functions. They can be invaluable, and well worth your time to get comfortable with them. You can use a CTE with Row_Number(), but the drawback is that you get an extra field. For me, the WITH TIES is cleaner for such requirements. Regarding your other SQL problem, I would suggest posting another question. If I can't help, I'm sure someone will be able to. Please, please include sampe data, field names, and desired results. You'll get more attention with a proper question. – John Cappelletti Jan 04 '18 at 01:49
  • @JohnCappelletti Thanks for the explanation! I have posted another question on here, which refers to: https://stackoverflow.com/questions/48100881/duplicate-the-rows-based-on-some-criteria-in-sql-or-r – Akira Jan 04 '18 at 17:53
  • @AkiraKaneshiro Taking a peek now – John Cappelletti Jan 04 '18 at 17:57