CODEWITHSUNDEEP
sqlsql-server
spacerobot
spacerobot

Reputation: 297

Having an issue with selecting max rows by date

I am trying to select the max timestamped records from table 1 based on some data from table 2. I am getting the correct records based on the where limits I have put on the query, but I am still getting duplicate entries not the max time stamped entries. Any ideas on what is wrong with the query?

Basically the ID 901413368 has access to certain leveltypes and I'm trying to find out what the max dated requests were that were put in for that same person for the leveltypes that person manages.

SELECT  
MAX(timestamp) AS maxtime, Leveltype, assign_ID
FROM       
WHERE 
(leveltype IN
(SELECT leveltype FROM  dbo.idleveltypes  WHERE   (id = 901413368)))
GROUP BY timestamp, assign_ID, leveltype
HAVING   (assign_ID = '901413368')

UPDATE: The issue has been resolved by WEI_DBA's response below:

Remove the timestamp column from your Group By. Also put the assign_ID in     the Where Clause and remove the Having clause

Upvotes: 1

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The following may be what you want. It should also be a simpler way to write the query:

SELECT MAX(a.timestamp) AS maxtime, a.Leveltype, a.assign_ID
FROM dbo.q_Archive a JOIN
     dbo.idleveltypes lt
     ON a.leveltype = lt.leveltype AND
        a.assign_ID = lt.id
WHERE assign_ID = 901413368
GROUP BY assign_ID, leveltype;

Notes:

  • Filter on assign_ID before doing the group by. That is much more efficient.
  • A JOIN is the more typical way to represent the relationship between two tables.
  • The JOIN condition should be on all the columns needed for matching; there appear to be two.
  • I don't understand why the leveltype table would have a column called id, but this is your data structure.
  • The GROUP BY does not need timestamp.
  • Decide on the type for the id column that should be 901413368. Is it a number or a string? Only use single quotes for string and date constants.

Upvotes: 3

McNets
McNets

Reputation: 10807

Remove timestamp from GROUP BY clause due you're getting MAX(timestamp)

You shoud not add aggregated fields to GROUP BY clause.

SELECT        
    MAX(timestamp) AS maxtime, 
    Leveltype, 
    assign_ID
FROM
    dbo.q_Archive
WHERE
    (leveltype IN (SELECT        leveltype
                           FROM            dbo.idleveltypes
                           WHERE        (id = 901413368)))
GROUP assign_ID, leveltype
HAVING        (assign_ID = '901413368')

Upvotes: 1

Related Questions