CODEWITHSUNDEEP
sqlsql-servermax
user3147424
user3147424

Reputation: 3662

Select Row with multiple max values

For each order number, I need to select the maximum record and then select the maximum number for each record.

QueryResults

I just want one row, which is shown.

Results

Here is my code so far, how could I select what I need?

SELECT number, record, order
FROM Table1

Upvotes: 0

Views: 64

Answers (4)

mohan111
mohan111

Reputation: 8865

Hi this is the another way to do to get the result 

select MAX(t.number),MAX(t.record),t.ORDERNo from @table t
INNER JOIN @table tt
ON tt.ORDERNo = t.ORDERNo
AND tt.Number = t.Number
AND t.Record = tt.Record
GROUP BY t.ORDERNo

Upvotes: 0

amit_g
amit_g

Reputation: 31250

If the Number is not always increasing and thus could have different MAx within each Record group, you have to do it in two steps i.e. first get MAX(Record) and then get MAX(Number).

SELECT T.[Order], Max(Number) AS MaxNumber, MaxRecord
FROM
(
    SELECT [Order], Max(Record) AS MaxRecord
    FROM Table1
    GROUP By [ORDER]
) T
INNER JOIN Table1 T1 ON T.[Order] = T1.[Order] AND T.MaxRecord = T1.Record
GROUP By [ORDER]

Upvotes: 2

Martin
Martin

Reputation: 16433

Assuming your table is called Table1, the following should give you what you are looking for:

SELECT  MAX(number),
        A.record,
        [order]
  FROM  Table1 A
    INNER JOIN (  SELECT  MAX(record) record
                    FROM  Table1) B ON A.record = b.record
  GROUP BY A.record, [order]

This will effectively find the maximum record and then return the maximum number for this record.

Upvotes: 0

Twelfth
Twelfth

Reputation: 7180

Order really isn't a good column name as it's a reserved word.

SELECT max(number), max(record), order
FROM Table1
group by order

This might not be right, I'll need some clarification. Your question is a bit confusing...this will return the max number and record for each order number. Were you looking for the max record in reference to the max number? IE:

555 777 1

666 555 1

In this case, would you expect to see 666 777 1, or 555 777 1?

Upvotes: 0

Related Questions