Prev: Index Rebuild and Statistics
Next: How to combine two identically structured tables to query them
From: ben brugman on 15 Jul 2010 09:26
Hello,

Goal: Find the row with the highest of lowest value (can be with extra
conditions).

Example 1 find the row with the highest value.
Example 2 find the row with the highest value not exceeding 3.5.

What is the most elegant (???) way to do this ?
The examples are simple enough, but if they become part of a larger query,
the solutions become less elegant.

(In example 2 the condition is repeated which I consider less elegant.
In the second solution for example 2, the with is used this only works in
recent versions of SQL-server and not in other SQL implementations)

See below for an example

Thanks for your time and attention,
Ben Brugman


-- drop table TAB

CREATE TABLE TAB
(
AAA int NULL,
BBB varchar(10) NULL
)
insert into TAB values(1, 'one')
insert into TAB values(2, 'two')
insert into TAB values(3, 'three')
insert into TAB values(4, 'four')
insert into TAB values(5, 'five')
-- select * from TAB

-- Example 1 find the row with the highest value.
-----------------------------------------------------------------------
--
-- Select the highest value
--

SELECT '--' as [--],* FROM TAB WHERE
aaa in (select max(AAA) from tab)

-- results in
-- AAA BBB
---- ----------- ----------
-- 5 five

-- Example 2 find the row with the highest value not exceeding 3.5.
-----------------------------------------------------------------------
--
-- Select the highest value (use an extra condition)
--

SELECT '--' as [--],* FROM TAB WHERE
aaa < 3.5
AND
aaa in (select max(AAA) from tab WHERE aaa < 3.5)

-- results in
-- AAA BBB
---- ----------- ----------
-- 3 three

-----------------------------------------------------------------------
--
-- Select the highest value (use an extra condition)
-- Using a common_table_expression
--
;
WITH
MOST AS (select max(AAA) as plup from tab WHERE aaa < 3.5)
SELECT '--' as [--],* FROM tab where aaa in (select plup from most)

-- results in
-- AAA BBB
---- ----------- ----------
-- 3 three


From: Erland Sommarskog on 15 Jul 2010 17:43
ben brugman (ben(a)niethier.nl) writes:
> Goal: Find the row with the highest of lowest value (can be with extra
> conditions).
>
> Example 1 find the row with the highest value.
> Example 2 find the row with the highest value not exceeding 3.5.
>
> What is the most elegant (???) way to do this ?

Probably with the row_number unfunction:

WITH numbered AS (
SELECT ..., rowno = row_number () OVER (ORDER BY val DESC)
FROM tbl
)
SELECT ...
FROM numbered
WHERE rowno = 1


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Index Rebuild and Statistics
Next: How to combine two identically structured tables to query them