Need Help With SELECT Query

Stryfe · Jun 4, 2008

I have a table of users that has records entered in it every hour with their updated "credit" which either increase or stays the same.

I want to write a select statment that returns the data in the row that contains the largest value for credit for each individual user on a particular day.

It will also sort by credit but that parts easy.

Here's what I have

SELECT userid, name, MAX(currentcredit) as current_credit, rac, country FROM userstats
WHERE month='6' AND day='4' AND year='2008'
GROUP BY userid
ORDER BY current_credit DESC

This give me the maximum credit for each user but the other columns(rac is what I really need to be from the matching row) it returns aren't from the row that contains the maximum credit amount, they're from the first row that userid is found in.

Krypto · Jun 5, 2008

Doesn't the SELECT DISTINCTROW statement do that.... geeze I really can't remember its been a long time.

Stryfe · Jun 5, 2008

DISTINCTROW can only be used if you're querying more than one table.

Krypto · Jun 5, 2008

Try adding all the other fields (name, rac, country) into the group

Stryfe · Jun 5, 2008

If I do that I get multiple rows per user, one for each time one of the other columns is unique (rac is different in most records for the same userid).

Stryfe · Jun 5, 2008

I think I need to use a join or a sub-query but I'm not sure how to set it up

YellowDart · Jun 5, 2008

So are you writing multiple records to the database of the same person? Or are you updating your records when at each interval?

/edit

Yea, I just noticed you're using an aggregate function there in your select. You likely will need a subquery...

Code:

SELECT
    U.username,
    ( SELECT
          MAX(credits)
          FROM userstats
          WHERE U.username = username ) AS 'current_credits'
    -- other fields go here
FROM userstats U
WHERE month='6' AND day='4' AND year='2008'
GROUP BY userid
ORDER BY current_credit DESC

YellowDart · Jun 6, 2008

One additional question I have for you:

Why are you hard coding the date into the query? Why not just pass in a parameter? Then just compare a date field to your parameter...

Code:

DECLARE
@CurrentDate datetime
AS
SELECT
    U.username,
    ( SELECT
          MAX(credits)
          FROM userstats
          WHERE U.username = username ) AS 'current_credits'
    -- other fields go here
FROM userstats U
WHERE date = @CurrentDate
GROUP BY userid
ORDER BY current_credit DESC

Need Help With SELECT Query

Stryfe

Member

Krypto

Member

Stryfe

Member

Krypto

Member

Stryfe

Member

Stryfe

Member

YellowDart

Member

YellowDart

Member

Similar threads