Try to figure this one out? Getting one column value based on order of another.

ss1289 · #1 (**permalink**) 04-18-11, 13:17

Hi,
I'm trying to build a query that will pull a single primary key id based on the minimum value of another column which is grouped by a foreign key.

For Ezxample:

Code:

Column 1 | Column 2 | Column 3
(PK)         (FK)         (Year)
-------------------------------
1111         101           2008
1222         101           2007
1333         101           2010

I want to get the PK id of 1222 because it's the smallest Year of FK 101.

My initial query was to select the smallest PK, but that was assuming that the PKs were created in order by year. However 2007 could be created after 2008.

shammat · #2 (**permalink**) 04-18-11, 17:02

Code:

SELECT *
FROM the_table_with_no_name
WHERE column_3 = (SELECT min(t2.column_3)
                  FROM the_table_with_no_name t2
                  WHERE t2.column_2 = 101)

Internet Services

Web Development

Digital Marketing

Consumer Tech