SQL select all columns to return only with MAX seqno

Question

Below query returns multiple rows as you can see in the image below.

I want to know how to return only one row with MAX seqno on it. Also note that I want to return all columns from the table here.

Appreciate you help.

SELECT * 
FROM dbo.SALESORD_HDR HD 
JOIN dbo.SALESORDHIST AS HI ON HD.SEQNO = HI.HEADER_SOURCE_SEQ
                            AND hi.FILEURL <> '' 
                            AND HI.EVENT_TYPE='D'

Do you need to return just `SEQNO` column or all the columns? — Ilyes, Feb 03 '19 at 21:17
Possible duplicate of [Selecting a Record With MAX Value](https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value) — Dale K, Feb 03 '19 at 21:23
I read that question but can not find instance where it can be applied to return all columns with join in the query — Crysis Hhtht, Feb 03 '19 at 21:30

score 5 · Answer 1 · answered Feb 03 '19 at 21:47

How about ordering the records and selecting only the first one?

SELECT TOP 1 * 
FROM dbo.SALESORD_HDR HD 
JOIN dbo.SALESORDHIST AS HI ON HD.SEQNO = HI.HEADER_SOURCE_SEQ
                            AND hi.FILEURL <> '' 
                            AND HI.EVENT_TYPE='D'
ORDER BY HD.SEQNO DESC

Derviş Kayımbaşıoğlu · Answer 2 · 2019-02-03T21:38:00.550

1

This would work.

select * 
FROM dbo.SALESORD_HDR HD 
JOIN dbo.SALESORDHIST AS HI 
   ON HD.SEQNO = HI.HEADER_SOURCE_SEQ and hi.FILEURL <> '' AND HI.EVENT_TYPE='D'
WHERE HD.SEQNO = (
  SELECT MAX(HD.SEQNO) FROM dbo.SALESORD_HDR HD 
JOIN dbo.SALESORDHIST AS HI 
   ON HD.SEQNO = HI.HEADER_SOURCE_SEQ AND hi.FILEURL <> '' AND HI.EVENT_TYPE='D'
)

edited Feb 03 '19 at 21:38

answered Feb 03 '19 at 21:22

Derviş Kayımbaşıoğlu

28,492
4
50
72

this still returns multiple columns. – Crysis Hhtht Feb 03 '19 at 21:25
does HD.SEQNO unique? without knowing your table structure it is hard to understand what you need. check my edited answer again please – Derviş Kayımbaşıoğlu Feb 03 '19 at 21:37
then joined table must have multiple rows – Derviş Kayımbaşıoğlu Feb 03 '19 at 21:51
1

used inner join instead of join and it worked. Thanks for the guidance. – Crysis Hhtht Feb 03 '19 at 22:28

SQL select all columns to return only with MAX seqno

2 Answers2