SQL Server Performance Forum – Threads Archive

Filter w/in 2 columns

I need to do a query that selects the lowest value in column x where column y = some value. If result count of column x > 1 then, and only then, select the lowest value in column z. I figured out how to select the lowest value in column x where column y = some value, but am unable to select the appropriate record in column z if more than one value exists in column x.
Pseudo Code:
Select * From myTable where columnY = someValue and columnX = (select min(columnX) From myTable where columnY = someValue) from here I can’t just find the lowest value in column z (as I did for columnX) because if the lowest value in column z is not the lowest value in column x this is not what I want. I’d really appreciate any help, I’m using MS Access and have been trying to get this to work for quite some time…

---

You’re asking a SQL Server community … We have the luxury of procedural T-SQL, whereas Access lets you run only a single statement … Anyway, let’s see what I can come up with … SELECT IIf(GaGa.BlaBla <= 1, GaGa.BlaBla, (SELECT MIN(z) FROM Tbl))
FROM (SELECT MIN(x) AS BlaBla FROM Tbl WHERE Tbl.y = ‘abc’) AS GaGa You may find that Access reformats that as SELECT IIf(GaGa.BlaBla <= 1, GaGa.BlaBla, (SELECT MIN(z) FROM Tbl))
FROM [SELECT MIN(x) AS BlaBla FROM Tbl WHERE Tbl.y = ‘abc’]. AS GaGa … just because it thinks it looks nicer that way. Anyway – the first thing Access will do is to run the query in the FROM part, a.k.a. the derived table. Next, it calls the IIf() function from VBA to evaluate the result of the derived table: if false, the result of the other subquery on the z column is returned. You must keep in mind that the IIf() function ALWAYS evaluates both the true and false parts, so it will ALWAYS run the subquery from the false part as well. This means the query may take longer than you expect. Finally, you didn’t say the MIN(z) query should be filtered for the same value on y. If it does, then you can do it in a somewhat simpler query: SELECT IIf(Gaga.BlaBla <= 1, Gaga.BlaBla, Gaga.HiHi)
FROM [SELECT MIN(x) AS BlaBla, MIN(z) AS HiHi FROM Tbl WHERE Tbl.y = ‘abc’]. AS GaGa

---

]]>