-1

I know we already have a few posts on similar topic. But I think this case a bit different and actually I couldn't get the result I wanted by answers given in other posts.

We have a table as below:

id   code   amount 
------------------
1     A1      80
2     A1      75
3     A1      70
4     A1      70
5     A1      70
1     A2      92
2     A2      85
3     A2      79
4     A2      50
5     A2      50

How can I select the row for "A1" and "A2" based on first lowest value (from top) on "Amount" column? In this case I want the result like below:

id   code   amount 
------------------
3     A1      70
4     A2      50

Thanks!

sqluser
  • 393
  • 1
  • 4
  • 22
  • 1
    Possible duplicate of [SQL query to select distinct row with minimum value](http://stackoverflow.com/questions/15291506/sql-query-to-select-distinct-row-with-minimum-value) – zero323 Dec 03 '15 at 06:39
  • do you need column `id` as well in `select query`? If yes, what is the dataType of that? – Pranav Bilurkar Dec 03 '15 at 08:59
  • Yes, I need column id - type is "int". And actually there are many more columns to select as well. – sqluser Dec 03 '15 at 10:26

2 Answers2

1

Use ROW_NUMBER:

SELECT
    id, code, amount
FROM (
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY code ORDER BY amount, id)
    FROM tbl
) AS t
WHERE Rn = 1
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
1

you don't need ordering by id. so correct way would be :

SELECT
    id, code, amount
FROM (
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY code ORDER BY amount)
    FROM tbl
) AS t
WHERE Rn = 1
Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20