SQL query to select highest ID only where field is equal to value

Question

Table 'section':

enter image description here

I am trying to create a query that does the following:

SELECT stop 
FROM section 
WHERE the id is the highest and the section is JPS_44-300-A-y10-1-1-1-H.

In the table above the result would be 1900HA080909. How can I do this using SQL?

Possible Duplicate http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column -> check that site for more information — Xatenev, Mar 21 '14 at 08:55

score 2 · Accepted Answer · answered Mar 21 '14 at 08:58

2

You can do this with the LIMIT function:

SELECT stop 
FROM section 
WHERE section='JPS_44-300-A-y10-1-1-1-H'
ORDER BY ID DESC
LIMIT 1;

answered Mar 21 '14 at 08:58

Praxis Ashelin

`TOP` is irrelevant and wrong here, since it is needed for mysql ;) – DrCopyPaste Mar 21 '14 at 08:59
@DrCopyPaste I understand he is indeed using mysql. How is it wrong? – Praxis Ashelin Mar 21 '14 at 09:00
1

because it wont work in mysql and you did not include information that the first approach wont work in mysql but only in tsql – DrCopyPaste Mar 21 '14 at 09:01

score 1 · Answer 2 · answered Mar 21 '14 at 08:56

1

Please try:

select * From YourTable a
where a.ID=(select MAX(ID) from YourTable b where b.Section=a.Section)

answered Mar 21 '14 at 08:56

TechDo

score 0 · Answer 3 · edited Mar 21 '14 at 09:01

0

SELECT stop FROM section
INNER JOIN (SELECT MAX(ID) FROM section WHERE section = 'JPS_44-300-A-y10-1-1-1-H') AS Tbl
 ON section.ID = Tbl.ID

edited Mar 21 '14 at 09:01

answered Mar 21 '14 at 09:00

Paul Lucaciu

An inner join seems a bit overkill for the simple query he needs. – Praxis Ashelin Mar 21 '14 at 09:03
I just wanted to show him that there are more ways in which he can achieve that. – Paul Lucaciu Mar 21 '14 at 09:07

score 0 · Answer 4 · answered Mar 21 '14 at 09:01

0

Try like that

SELECT a.stop FROM section a WHERE a.id IN (
     SELECT MAX(id) FROM section b
    WHERE b.section = 'JPS_44-300-A-y10-1-1-1-H'
)

answered Mar 21 '14 at 09:01

Wundwin Born

4 Answers4