Finding a row with the heigest/lowest value under specific conditions.

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 


0
ben
7/15/2010 1:26:43 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
614 Views

Similar Articles

[PageSpeed] 17

ben brugman (ben@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@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

0
Erland
7/15/2010 9:43:41 PM
Reply:

Similar Artilces:

Search and find test
I want to find all cells that contain whatever text I am searching for. Ctrl+F will do this and display everything it finds in a separate window. What I would like to do is have all of the cells and contents in the row displayed that the search finds. .. Excel 2007 only List content and location of finds. http://www.mediafire.com/file/ozihcjmdmko/02_25_10.xlsm ...

Insert rows and fill formulas
I have just added this on a work sheet. It works OK when sheet is unprotected but when I protect the sheet it comes up error 400. If I select Insert row on protection box it will insert row but not formulas while protected. How can I get it to work while sheet is protected. I have also setup a botton on the taskbar to do this for me but it will only do it for the day I set it up in. When I save as for the next day it wont work on the new sheet. Your help will be appreciated Regards Chris I'm not an expert but I thought the whole idea of protecting the worksheet is so p...

Conditional Formatting Problem #7
Is there a way to make conditonal formatting work when there is a formula in the cell? Conditional foramtting works if there is no formula in the cell. If one sheet is linked to another that doesn't work either. Is there a way to get around this? Under conditonal formatting there is a conditon "formula is." What would you put in there to have the cell format they you would like? There are two different things: a) the cell, containing a cell formula, a value or nothing b) the condition, containing a boolean formula (formula is) - b) can refer to a), but a) referring to a...

limit number of rows 7 colloms in a worksheet
is there a way to limit or set the number of rows & collums in a worksheet ? thanks david --- Message posted from http://www.ExcelForum.com/ "davidbrowne17" <davidbrowne17.ya1sm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya1sm@excelforum-nospam.com... > is there a way to limit or set the number of rows & collums in a > worksheet ? No. All worksheets have 256 columns by 65536 rows. You can hide unused rows/columns. But why bother? Hi David, Put this in the ThisWorkbook code module. Adjust to suit the area. Private Sub Workbook_Open() Wo...

Lookup and return value
I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith On worksheet 2, cell b2: =INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)) With error trapping: =IF(ISNUMBER(MATCH(A2,'Worksheet 1'!B:B,0)),INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)),"") ...

Version info with condition
Hi Everyone, I have a project that needs to be compiled under two different names. I have a preprocessor _X_ for one and _Y_ for the other. The part where I'm having problems with is the Version information. I have two version information (VS_VERSION_INFO) one with _X_ in the condition property and one with _Y_ Well, with either build I don't get any version information in the exe. Any ideas? AliR. Have you defined _X_ or _Y_ in the "Resources" section of the project's properties? (Not just in the C/C++ section) On 15 feb, 18:14, "AliR \(VC++ MVP\)"...

Where do I receive or find a delivery receipt?
To test how delivery receipt works I sent an email to myself, requesting delivery receipt, (so I know that it was received) but what form does delivery receipt come in? Same thing with read receipt, I sent myself one, respond yes when I opened the message, but haven't received anything I can see that is a read receipt. Where would I find it? Delivery receipts are generated by the recipient's mail server, if it is supported. As for the read receipt, have you hit send/receive after reading the email? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the d...

Data labels: need value AND % change
I'm working on a series of column charts that show annual data for # sold, $ volume, average price, median price, etc. I want it to show the 2002 and 2003 values for each of these but I ALSO want to be able to show the % change between the 2, so we can see trends. So far, I only see that you can label the data with the value, or the percentage. I haven't seen how you can show the values AND the % change. Any ideas? I'd really appreciate it! --- Message posted from http://www.ExcelForum.com/ Create the desired label in a separate column (the 2003 price concatenated with t...

Conditional Formatting #4
Hello All How would one go about conditional formatting Sheet 2 A1 if Sheet 1 A1 said TRUE ?? Thanks in advance Ian ...

Allow Multiple Values check box not showing up
I am trying to create a Multivalued Lookup Field. I am using the Lookup Wizard, but when I get to the place where is should let me choose to store multiple values for the lookup that section does not come up. I went online and printed the instructions off the help page so I know I am doing it right. Help! IMO, multivalued and lookup fields are not a good idea. You didn't mention what version of Access you are using. -- Duane Hookom Microsoft Access MVP "mschurter" wrote: > I am trying to create a Multivalued Lookup Field. I am using the Lookup > ...

Sum & report contents of table containing text values?
Can excel summate and report in charts text information recorded in a single table. EG Columns entitled Hair, Eyes, Height with values blue brown green auburn blond dark tall short medium. etc -- Neil You could use a pivot table to summarize the data, and create a chart from that. There are instructions and links here: http://www.contextures.com/xlPivot01.html For example, put Hair in the row area and Eyes in the column area. Put Height in the data area, as Count of height. Neil wrote: > Can excel summate and report in charts text information recorded in a single > table. ...

Newbie question: finding aliases and forwards
If I have an address such as services@somewhere.com how could I figure out where (which user) that address exists at in Exchange or where it forwards to (if it forwards versus belonging to a user)? Thanks. just go to Active Directory Users and computers and do a find with a filter by the users email Address "Joe Blow" wrote: > If I have an address such as services@somewhere.com > how could I figure out where (which user) that address exists at in Exchange > or where it forwards to (if it forwards versus belonging to a user)? > Thanks. > > > ...

Finding Transactions
I am using Money 06. Is there a way for me to locate transactions across my accounts where the 'Category' is blank? Thanks Transactions by Category report customized Category|Clear All, Category|Include unassigned income transactions, Category|Include unassigned expense transactions, Date|All Dates, Account|Select All, etc. "Bill" <Bill@discussions.microsoft.com> wrote in message news:24D7BE0D-2C92-42B2-8E58-BCF2202096D1@microsoft.com... >I am using Money 06. Is there a way for me to locate transactions across my > accounts where the 'Category' is bl...

I did install Genuine Advantage ActiveX control. But why could not find it in my IE Add-ons List?
Several days ago, I downloaded Windows Mobile Device Center from Micorosft web site for my Windows 7. As it needed Windows Genuine Verification, I installed the Genuine Advantage ActiveX control for my IE8. But now, I can not find this activeX control in my IE Add-ons List( IE -> Tools -> Manage Add-ons options of Internet Explorer ). Anyone could tell me why and where? Thanks in advance. It installed and it is not removable in any shape or form. Once done (Genuine Advantage ActiveX) it is done :-) -- Peter Please Reply to Newsgroup for the benefit of oth...

mail does not find temporay directory to open attached .xls file
Since few weeks, I cannot open attached .xls file, for the temporary directory is not found. I bypass it by saving the xls file in a a folder and then open it from there. It does't work if saved on desktop. All other attached files, pps or world, are ok -- Are all your Windows Updates current? I seem to recall there was an update for IE8 a couple of months ago which fixed the failure to open attachments. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "escofie" <escofie@discussions.microsoft.com&...

Jump to start of next row teaser
Hi, can anyone assist in telling me if there is either :- a) a shortcut key b) a macro c) none of the above but another solution to jumping to the beginning of a new row on pressing a key. For example, after completeing cell m10 can you press enter (or any other key) and the cursor will automatically go to cell a11, then after you get to m11 it jumps to a12 and so on? Just to make things a little trickier, I know you can do this using the protect worksheet etc function, but I also have the data filter running which doesn't work when the protect sheet function is on. If there is a macr...

finding differing numbers.
How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5 Thanks Chris Hi one way: - select your column - choose 'Data - Filter Advanced Filter' - choose a new range and 'unique entries' -- Regards Frank Kabel Frankfurt, Germany curleyc wrote: > How do I in a column of numbers some in duplication, how can i get a > list off the entries which reflects these numbers but not in > duplicat...

where do i find autoarchive on my computer
I have Winows XP and have no idea how to find the archive file. I used to have an icon on the desk top but that has disappeared. Any help would be appreciated. Your question does not have anything to do with Word. Autoarchiving is a feature of Outlook. Was archiving of mail items that you were thinking of? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Ganga" <Ganga@discussions.microsoft.com> wrote in mess...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

Specific Email redelivers itself
I am running windows 2003 exchange as my email server. We also use an email/web filtering software called surfcontrol. This sits at the server and using rules hold's certain emails (i.e. mainly spam). We have a size rule where if an email is over 8mb, surfcontrol holds onto it until myself or my colleague can check the size/content. Back in Feb, one of my users received a legit 12mb powerpoint presentation. As this was fine I released the email from the filter, however he did not get it. The sender (external to the company), resent the email about 4 times before the user informed me ...

Cannot Install / Find Driver for Microsoft 2.4GHz Transceiver USB?
I recently purchased a Microsoft Comfort 5000 wireless keyboard and mouse. I can successfully install the software associated with the keyboard and mouse, but when I plug in the USB Transceiver device Windows Install shield tells me "the hardware was not installed because the wizard cannot find the necessary software'. I've tried manually to search the disk and my hard drive and it still cannot find the software. It doesn't matter if I plug the USB device into any of the USB slots on my PC. I'm running Windows XP and have had similar install problems with ...

Is there any easy way to find out the version of MDB file ?
We are using MS Access 2003. We just find that there is a MDB file that hasn't been used for a number of years. We would like to know is there any easy way to find out which version of MS Access (Like Access 97 / Access 2.0) it is created ? When we open it, it asks us to "Convert" or "Open it". Thanks http://www.microsoft.com/downloads/details.aspx?FamilyID=2e861e76-5d89-450a-b977-980a9841111e&DisplayLang=en may be of use. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Peter" <Peter@discussions.microsoft...

Duplicate Row
i dont know how to make excel automatically highlight a duplicate row any tips on this? cos its very tedious to look for duplicates on th worksheet. :confused -- Message posted from http://www.ExcelForum.com Katkat, take a look at this page from Chip Pearson's Website: http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates. -- DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "katkat >" <<katkat.15p2xe@excelforum-nospam.com> wrote in message news:katkat.15p2xe@excelforum-nospam.com... > i dont know how to make excel au...

Generate random value
Dear All, In order to get random code label i used RAND function, unfortunately i could not get the correct value. What value i want to get is all value greater than 100 and less than 1000 with no number duplicated consecutively for instance 110, 199,111, etc. Just number like 102, 121 or 123 etc. What appropriate RAND function i should applied? Highly appreciate for helping. regards \benny =RANDBETWEEN(100,1000) use the =RANDBETWEEN(100,1000) function. To do the rest perhaps filter by unique values and do a sort? But then it isn't really random if you want 900 consecutive number...

How do I select multiple rows randomly in MS Excel?
How do I select multiple rows randomly in MS Excel? Hi Select your first row and then hold down the Ctrl key while selecting the others. HTH Michael "Varun" wrote: > How do I select multiple rows randomly in MS Excel? "Varun" <Varun@discussions.microsoft.com> wrote > How do I select multiple rows randomly in MS Excel? Just another angle to the post's interp .. (with emphasis on "randomly") Here's an example set-up to play with .. Assume we have 6 rows of data below in Sheet1's A1:C6: Data1 Text1 Desc1 Data2 Text2 Desc2 Data3 Text...