I have a table where i need to select the max value of one column

Question

Ive found a couple solutions one in particular that was of great help, and nearly got me there, but im stuck on the last bit. All i need to do now is be able to specify a range for the CarNumbers to be displayed.

the SQL might make more sense...

SELECT sr.*  FROM StatusReport sr 
INNER JOIN (  SELECT CarNumber, MAX(StatusReportTime)  AS MaxDateTime
FROM StatusReport  GROUP BY CarNumber )  groupedsr ON sr.CarNumber =
groupedsr.CarNumber  AND sr.StatusReportTime = groupedsr.MaxDateTime

I basically want to be able to add the following line in.

WHERE CarNumber BETWEEN '0' AND '3999'

Could anyone help me out?

Thanks in advance,

Mike

what rdbms youare using? MySQL? SQLServer? Oracle? .... – John Woo Jan 06 '13 at 09:58 — John Woo, Jan 06 '13 at 09:58

score 1 · Accepted Answer · answered Jan 06 '13 at 07:31

1

You can add it at the end of the outer query, or at the end of the subquery before the GROUP BY clause like so:

SELECT sr.*  
FROM StatusReport sr 
INNER JOIN 
(  
  SELECT CarNumber, MAX(StatusReportTime)  AS MaxDateTime
  FROM StatusReport  
  WHERE CarNumber BETWEEN '0' AND '3999'
  GROUP BY CarNumber 
)  groupedsr  ON sr.CarNumber        = groupedsr.CarNumber  
             AND sr.StatusReportTime = groupedsr.MaxDateTime;

answered Jan 06 '13 at 07:31

Mahmoud Gamal

78,257
17
139
164

Also could do it by `HAVING` in the `INNER JOIN` subquery. – jdotjdot Jan 06 '13 at 07:42
thanks so much for all the answers, it makes alot more sense to me now. thanks again! – Mike Jan 06 '13 at 08:17
1

@jdotjdot - Yes you are right. But since you are not filtering on the aggregated column something like `HAVING MAX(status) < ...` then it is better to do this using a `WHERE` clause. – Mahmoud Gamal Jan 06 '13 at 08:23
Ok so further more i have another question. When i run the query, it works fine when i look for say 0 - 3999, but when i search 7101-7199 i get results as follows: 7, 71, 711, 7189 etc. it seems to be searching through each digit in the query. i specifically need just 7101-7199. – Mike Jan 07 '13 at 22:42
@user1952458: Is it an integer? Use integers, not strings. `BETWEEN 7101 AND 7199` – Ry- Jan 08 '13 at 02:46
@user1952458 What is the datatype of that column `CarNumber`? – Mahmoud Gamal Jan 08 '13 at 08:09

score 0 · Answer 2 · edited Jan 06 '13 at 07:56

0

SELECT sr.*
FROM StatusReport sr
INNER JOIN
  (SELECT CarNumber,
          MAX(StatusReportTime) AS MaxDateTime
   FROM StatusReport
   WHERE CarNumber >0
   GROUP BY CarNumber) groupedsr ON sr.CarNumber = groupedsr.CarNumber
AND sr.StatusReportTime = groupedsr.MaxDateTime

edited Jan 06 '13 at 07:56

Kijewski

25,517
12
101
143

answered Jan 06 '13 at 07:32

Nour Berro

550
5
14

Hardik Mer · Answer 3 · 2013-01-06T08:00:58.050

0

you can do it like this.. this will be easy to understand and will simplify your query

    with tbl2 as
    (
    select tbl1.*,
    row_number() over (order by CarNumber) rtn 
    from 
    (select CarNumber, max(StatusReportTime) as MaxDateTime from [StatusReport] group by         
    CarNumber) as  tbl1
    )
    select  * from tbl2 where rtn between 0 and 3999

edited Jan 06 '13 at 08:00

answered Jan 06 '13 at 07:54

Hardik Mer

824
4
14
26

I have a table where i need to select the max value of one column

3 Answers3