-1

I have this table

| sale_id | amount |
| ------- | ------ |
| 5       | 3      |
| 1       | 2      |
| 3       | 1      |

And i need select JUST the sale_id of the max amount, just the number id 5 because 3 is the max amount. Sounds simple, but im having problems with this.

Can someone help me, please?

Cássio Fogarin
  • 1
  • 3
    Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Jan 13 '21 at 15:53
  • 3
    What if there was a sale_id 6 with amount = 3 as well? –  Jan 13 '21 at 15:54
  • Does this answer your question? [How to select top N from a table](https://stackoverflow.com/questions/3353446/how-to-select-top-n-from-a-table). I hope there's a lot of good answers here that can be improved over time (with new versions or DBMSes) instead of a lot of identical answers on identical questions. – astentx Jan 13 '21 at 15:59

3 Answers3

2

In standard SQL, this looks like:

select sale_id
from t
order by amount desc
fetch first 1 row only;

Not all databases support the fetch clause, but all have some mechanism for returning a result set with one row, such as limit or select top.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

MS SQL dialect

select top 1 sale_id
from tbl
order by amount desc
Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
1

In SQL server this can be achieved by:

SELECT TOP 1 sale_id FROM t order by amount desc

Incase you have duplicates max amount and need to fetch both sale_id then you can go for windowing function.

Pikun95
  • 140
  • 7
  • The second query will produce the error "not a group by" or something similar, because there's no `group by` part in the subquery. Also it will not answer the question: there will be max amounts per `sale_id`, not a single row – astentx Jan 13 '21 at 16:16
  • @astentx you are right.it will give a groupby error.I have removed it from answer :) – Pikun95 Jan 13 '21 at 16:27