CODEWITHSUNDEEP
sqlpostgresqlselect
Ahmed
Ahmed

Reputation: 1533

SQL select statement - return highest number value of a field

I have the following SQL select statement 

SELECT first_name, year FROM users WHERE username='$username'

This can return many results because a user record can exist per year e.g. one for 2012, 2011 etc. Therefore I want to only fetch the details of the user from the latest year that the user record exists for.

Any ideas how i can do this?

Thanks

Upvotes: 0

Views: 442

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

Simplest form for PostgreSQL:

SELECT first_name, year
FROM   users
WHERE  username = '$username'
ORDER  BY year DESC
LIMIT  1;

Upvotes: 0

user330315
user330315

Reputation:

There are several ways to do this:

If you only need this for a single user, then Avitus' answer (adjusted to Postgres using LIMIT instead of TOP) is probably the best one. Note that if a user has more than one row with the same "top" year, you'll see only one. Probably this is OK, if not the following solution would fix that.

If you need this for multiple users in a single statement, something like this is probably faster than using the co-related sub-query from user1988097 because only a single scan over the table is required. 

select first_name, 
       year
from (
   select first_name,
          year,
          max(year) over (partition by username) as max_year
   from users
) t
where year = max_year;

You can add as many columns to the result as you like, you need to do that in the inner and outer query though.

Upvotes: 0

Muhammad Hani
Muhammad Hani

Reputation: 8664

MySQL

SELECT first_name,MAX(year)
FROM users
WHERE username = 'username'

Other than it you have to add GROUP BY clause.

SELECT first_name,MAX(year)
    FROM users
    WHERE username = 'username'
    GROUP BY username

Upvotes: 0

Darius X.
Darius X.

Reputation: 2937

You can find the max year for a user, and then nest that so that you only select the one record with that year.

SELECT first_name, year FROM users U1 WHERE username='$username'
 and year = (select max(year) FROM users U2 WHERE U1.username=U2.username)

Upvotes: 1

Avitus
Avitus

Reputation: 15958

Select top 1 first_name, year 
from users 
where username ='$username' 
order by year desc

Upvotes: 2

Related Questions