[sql] who's the youngest username ?

[PROKA]

Ok so I have this table named "Users" with the fields 'username' and 'age'

 

I'm interested to know who's the youngest username.

How do I formulate the SELECT statement ?

[michael_hk]

If you are using SQL Server...

 

Select username from Users where age = (Select min(age) from Users)

[kejpa]

Select username from Users where age = (Select min(age) from Users)

Subselect, not too good. Use an aggregation instead:

SELECT username, Min(age) AS YoungestMember

FROM Users

GROUP BY Users.age;

 

/Kejpa

[cpopham]

Quick question, I understand that the subselect statement my add a small amount of overhead time to the server, but when you are talking about a Select statement this small how much time is it really wasting? I try to use the most efficient ways of doing things, but if you did not know another way of doing it, then what is wrong with the nested select statement? :) Just trying to learn some things here.

 

Chester

[Nerseus]

Here's another way to get the youngest person - this is SQL Server syntax, other languages use different syntax:

SELECT TOP 1 username, Age AS YoungestMember FROM Users
ORDER BY Age ASC

 

kejpa's method works if you want all people, by age - not just the youngest. The subselect method works fine though you can't control which user you get back other than one - more or less randomly - from the users that match the youngest age. If 32 people are all 14 years old then you'll get one of the 14, but you can't control which. The "TOP 1" with ORDER BY will let you have a little more control, if it's needed.

 

Performance of each? Kinda depends on the amount of data you have and disbersement of values. The ORDER BY is going to want to try to use an index, as will the MIN() on the subselect. Those types of operations will vary depending on the data being looked at - in this case Age. The SQL engine has an Optimizer that evaluates all the SQL and creates a plan for how it thinks it can get to what you want the fastest. If you've got on the order of a few thousand or less users you'll probably be fine.

 

-ner

[michael_hk]

kejpa's method works if you want all people, by age - not just the youngest. The subselect method works fine though you can't control which user you get back other than one - more or less randomly - from the users that match the youngest age. If 32 people are all 14 years old then you'll get one of the 14, but you can't control which. The "TOP 1" with ORDER BY will let you have a little more control, if it's needed.

-ner

The subselect method

 

Select username from Users where age = (Select min(age) from Users)

will return all (32) users who are 14.

[Nerseus]

You're right, my goof - I think I was thinking he still wanted just one username and that even with a sub-select he'd still want a TOP 1 or similar.

 

-ner