SQL to get the lowest unique value from table

Hi
I'm looking for some SQL to get the lowest unique value in a table:
eg,

<table>

ID BID
1 1
2 1
3 1
4 2
5 2
6 2
7 3
8 4

3 is the lowest unique value. I would need to return the ID.

Any suggestions?

thanks
Lee

0
Lee
1/2/2010 10:15:27 PM
sqlserver.programming 1873 articles. 0 followers. Follow

8 Replies
1578 Views

Similar Articles

[PageSpeed] 36

"Lee" <lee@digital-interactive.com> wrote in message 
news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com...
> Hi
> I'm looking for some SQL to get the lowest unique value in a table:
> eg,
>
> <table>
>
> ID BID
> 1 1
> 2 1
> 3 1
> 4 2
> 5 2
> 6 2
> 7 3
> 8 4
>
> 3 is the lowest unique value. I would need to return the ID.
>
> Any suggestions?
>
> thanks
> Lee
>
Hi Lee

Two possible options

SELECT id
FROM ids
WHERE bid = (
SELECT TOP 1 bid
FROM ids
GROUP BY bid
HAVING COUNT(*) = 1
ORDER BY bid )

or

SELECT TOP 1 i.id, i.bid
FROM ids i
LEFT JOIN ids j ON i.bid = j.bid AND i.id <> j.id
WHERE j.id IS NULL
ORDER BY bid

John 

0
John
1/2/2010 10:36:53 PM
John Bell wrote:
> 
> "Lee" <lee@digital-interactive.com> wrote in message
> news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com...
> > Hi
> > I'm looking for some SQL to get the lowest unique value in a table:
> > eg,
> >
> > <table>
> >
> > ID BID
> > 1 1
> > 2 1
> > 3 1
> > 4 2
> > 5 2
> > 6 2
> > 7 3
> > 8 4
> >
> > 3 is the lowest unique value. I would need to return the ID.
> >
> > Any suggestions?
> >
> > thanks
> > Lee
> >
> Hi Lee
> 
> Two possible options
> 
> SELECT id
> FROM ids
> WHERE bid = (
> SELECT TOP 1 bid
> FROM ids
> GROUP BY bid
> HAVING COUNT(*) = 1
> ORDER BY bid )
> 
> or
> 
> SELECT TOP 1 i.id, i.bid
> FROM ids i
> LEFT JOIN ids j ON i.bid = j.bid AND i.id <> j.id
> WHERE j.id IS NULL
> ORDER BY bid
> 
> John

a somewhat simplified version of the first option:

  SELECT TOP 1 MAX(id) AS ID
  FROM ids
  GROUP BY bid
  HAVING COUNT(*) = 1
  ORDER BY bid

-- 
Gert-Jan
0
Gert
1/3/2010 11:49:11 AM
Gert-Jan Strik (sorrytoomuchspamalready@xs4all.nl) writes:
> a somewhat simplified version of the first option:
> 
>   SELECT TOP 1 MAX(id) AS ID
>   FROM ids
>   GROUP BY bid
>   HAVING COUNT(*) = 1
>   ORDER BY bid

Should be MIN I think.... 



-- 
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
1/3/2010 4:49:01 PM
It doesn't matter, both MIN and MAX will work. The ORDER BY clause for the TOP 1 operator defines the min value, the 
aggregate function is just to retrieve the id value.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/3/2010 5:48:10 PM
>>I'm looking for some SQL to get the lowest unique value [of what?] in a table [with a name]  I would need to return the ID <<

WITH Singleton_Bids (bid_id, bid_amt)
AS
(SELECT MAX (bid_id), bid_amt
   FROM Auction
  GROUP BY bid_amt
HAVING COUNT(*) = 1)

SELECT S1.bid_id, S1,bid_amt  -- can be more than one!
  FROM Singleton_Bids AS S1
 WHERE S1.bid_amt
     = (SELECT MIN(S2.bid_amt)
            FROM Singleton_Bids AS S2);
0
CELKO
1/4/2010 1:29:36 AM
Go to the top of the class :)

"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B408437.124D0557@xs4all.nl...
> John Bell wrote:
>>
>> "Lee" <lee@digital-interactive.com> wrote in message
>> news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com...
>> > Hi
>> > I'm looking for some SQL to get the lowest unique value in a table:
>> > eg,
>> >
>> > <table>
>> >
>> > ID BID
>> > 1 1
>> > 2 1
>> > 3 1
>> > 4 2
>> > 5 2
>> > 6 2
>> > 7 3
>> > 8 4
>> >
>> > 3 is the lowest unique value. I would need to return the ID.
>> >
>> > Any suggestions?
>> >
>> > thanks
>> > Lee
>> >
>> Hi Lee
>>
>> Two possible options
>>
>> SELECT id
>> FROM ids
>> WHERE bid = (
>> SELECT TOP 1 bid
>> FROM ids
>> GROUP BY bid
>> HAVING COUNT(*) = 1
>> ORDER BY bid )
>>
>> or
>>
>> SELECT TOP 1 i.id, i.bid
>> FROM ids i
>> LEFT JOIN ids j ON i.bid = j.bid AND i.id <> j.id
>> WHERE j.id IS NULL
>> ORDER BY bid
>>
>> John
>
> a somewhat simplified version of the first option:
>
>  SELECT TOP 1 MAX(id) AS ID
>  FROM ids
>  GROUP BY bid
>  HAVING COUNT(*) = 1
>  ORDER BY bid
>
> -- 
> Gert-Jan 

0
John
1/4/2010 7:12:57 AM
Select min(BID) as BID from table1 Group by  BID Having count(*) =1

This gives the row with BID 3..

Now selet the ID

SELECT ID from Table WHERE BID = (
Select min(BID) as BID from table1 Group by  BID Having count(*) =1)



"Lee" <lee@digital-interactive.com> wrote in message 
news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com...
> Hi
> I'm looking for some SQL to get the lowest unique value in a table:
> eg,
>
> <table>
>
> ID BID
> 1 1
> 2 1
> 3 1
> 4 2
> 5 2
> 6 2
> 7 3
> 8 4
>
> 3 is the lowest unique value. I would need to return the ID.
>
> Any suggestions?
>
> thanks
> Lee
> 


0
Wayne
1/5/2010 11:07:43 AM
Wayne,

The statement

  Select min(BID) as BID from table1 Group by  BID Having count(*) =1

will not return one row (if there are at least two different BIDs with
each just one occurrence), as the following example demonstrates.
Because of that, your final statement will yield a runtime error.

  create table #table1 (ID int identity,BID  int)
  insert into #table1 VALUES (1)
  insert into #table1 VALUES (1)
  insert into #table1 VALUES (1)
  insert into #table1 VALUES (2)
  insert into #table1 VALUES (2)
  insert into #table1 VALUES (3)
  insert into #table1 VALUES (4)
  go
  
  Select min(BID) as BID from #table1 Group by  BID Having count(*) =1
  
  SELECT ID from #table1 WHERE BID = (
    Select min(BID) as BID from #table1 Group by  BID Having count(*)
=1)
  
  go
  drop table #table1

BID         
----------- 
3
4

(2 row(s) affected)

Server: Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.

-- 
Gert-Jan


Wayne Snyder wrote:
> 
> Select min(BID) as BID from table1 Group by  BID Having count(*) =1
> 
> This gives the row with BID 3..
> 
> Now selet the ID
> 
> SELECT ID from Table WHERE BID = (
> Select min(BID) as BID from table1 Group by  BID Having count(*) =1)
> 
> "Lee" <lee@digital-interactive.com> wrote in message
> news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com...
> > Hi
> > I'm looking for some SQL to get the lowest unique value in a table:
> > eg,
> >
> > <table>
> >
> > ID BID
> > 1 1
> > 2 1
> > 3 1
> > 4 2
> > 5 2
> > 6 2
> > 7 3
> > 8 4
> >
> > 3 is the lowest unique value. I would need to return the ID.
> >
> > Any suggestions?
> >
> > thanks
> > Lee
> >
0
Gert
1/5/2010 6:15:24 PM
Reply:

Similar Artilces:

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

Excel, how do I get ALT F C to work the same as ALT F Enter C ?
The above is an example, but it aplies to any menu. Before, if I pressed say, ALT F, the drop down menu would appear and I could press, say, C and get to the submenu. Now I have to press Enter, before the C, which is a bother. Thanks. On my Windows 2000 version 5.00.2195 with Excel '97 and Excel 2003 the Alt F C still works. What version software / Excel are you using? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www....

Trying to Get Subset of XML
I want to display a segment of an XML (see below) file in a datagrid. I want to select a specific <EventID> and get all of its children into a dataset so that I can bind a datagrid to those values. I am trying an expression as follows but I get an error "Value of type 'System.Xml.XmlNode' cannot be converted to 'System.Xml.XmlNodeList'." myNodes = xmldoc.ChildNodes("/Dataset/Events/[ShowName=Round 2]") What is the correct way to "get" that set of nodes and convert them to a dataset? ================== Portion of XML File ===============...

pivot table #21
I have a question on Excel. When I update a pivot table, I used to be able to hold down the shift and ctrl keys and highlight the area, but lately I found that I cannot use this short cut method. Is there another short cut method? Thanks for your help. In step two of the Pivot Wizard, you should be able to select a starting cell on the worksheet, then hold the Shift key, and tap the End key, then the Down or Right arrow key, to select a range of cells. Or, base the pivot table on a dynamic range, which will expand automatically as new records are added. There are instructions here:...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

How do I get excel files to open automatically from directories?
When I try to open excel files from the directory or from desktop I only get a blank worksheet not the file. I have to then go through File Open to get the file I want. There must be a way to have them open automatically. On Mon, 2 Jan 2006 21:22:01 -0800, Damian <Damian@discussions.microsoft.com> wrote: >When I try to open excel files from the directory or from desktop I only get >a blank worksheet not the file. I have to then go through File Open to get >the file I want. > >There must be a way to have them open automatically. Go to Tools -> Options -> Gen...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

I can't get rid of 1 "Payment to Send' Reminder-M07
On my Home Page in the Reminder section, I have a "1 Payment to Send" listed there. But there is NO payment to send. I"ve opened up back-up copies as far back as May and the reminder is there, but there is no payment that needs sending. Does anybody have an idea how I can get rid of this Reminder? Just removing the Reminder from Home Page doesn't do it. When you put the Reminder back on the Home Page, the "Payment to Send" is still there. Clicking on the link just takes me to Bill Summary page. ...

getting added to other peoples meetings in shared calendar
I opened shared calendars in my group by choosing their name and then the email messages went out giving them access to my calendar and requesting to view theirs. Ever since, I have been copied in as a "required attendee" on all meetings for everyone. I get the calendar items on my calendar and emails requesting Accept or Decline in my inbox. When others view view thru their outlook, they do not see me as a required attendee? What am I doing wrong? are you adding them as delegates or just giving them permission to view the calendar (by right clicking on the calendar fold...

Can Work thread get a windows class and manipulate it?
Suppose that I create a work thread when my dialog box started, then in the thread I get the pointer to the dialog mfc class and call updateData method. Does that work? I tried, but failed. Is there any other way to do? thanks!! No, it won't work, don't even waste time trying. It is almost guaranteed to fail. You do not manipulate the windows owned by one thread from a different thread. What you do is PostMessage requests from the worker thread to the main UI thread, usually to the window that contains the controls. The fact that you are using UpdateData already says you are in t...

Cannot get my CImageList to display images?
Ok i am successfully diplaying images in my tree controls but not my list controls. I am using the following but not image is being displayed??? int nIndex = mListCtrl.InsertItem(LVIF_IMAGE | LVIF_TEXT,0,"hello", 0,0,7,0L); Where 7 is a valid index into my image list! Please help? That really doesn't help much. Since you got the tree control working I'm assuming that you are calling the list control's SetImageIist. Also for debugging purposes call GetImageCount on your image list to see how many images it loaded. Let me also point out that you can call this ...

Create Login with T-SQL
Hello, I am creating, using T-SQL, a database with its file groups, tables, constraints, etc. Can I also create, using T-SQL, a Login with Username and Password to access that database? Usually, I go to Security > Logins of SSMS to manually create the login. However, if I could do the same using T-SQL I would have it all made automatically. Thanks, Miguel EVERYTHING that the GUI does winds up as tsql executions on the server. :-) Most of them you can generate a script for using the Script menu in the upper left of the current dialog window. This is a great way to ...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...

Don't get some emails;
Some emails don't come thru. From another computer I can forward messages to my email site but don't receive new messages. Also not getting some emails from various sites. ...

Putting Excel Charts into a Word document that's getting too big
I am creating a Word document (our Annual Report) in which I will have many charts and graphs that were created in Excel. Currently, there will be at least 26 graphs/charts in the document. In the past, there have apparently been problems with not having enough space to save the Word document because it gets too big. The document itself is only about 36 pages, but, as I mentioned, within the 36 pages are at least 26 graphs and charts. I'm looking for a way to copy the graphs/charts into Excel without it taking so much memory. One solution we thought of was to only paste the gra...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

Show / Hide items in Pivot Table as required
Hi I am using MS Excel 2003 and need a macro code for show only my selected items only in a Pivot Table. Query: One excel table empstatus.xls in which fields are Employee No , Department, Status, other fields. In status field items are Blank, Leave, Left, Resigned. I wanted to show only blank & leave based on when I’m selecting a particular month in my pivot table. So is there any code to hide all data items of status and only show blank & leave. Please suggest. -- ------------------------------ Thanks Nitesh ------------------------------ ...

Update table with Multi-select list box
I have a database with a tab control that has several pages in it (my boss loves tabs for navigation). Each page has a list box based on a category of training events that members of my office attend and the box is based on a query that selects the training events for the tab page's respective category. I select an item from the list, click a button, and go to a form with a text box showing the name of the training event I previously selected. On that form, I want to select mutiple names of office members from a list box (already created and source is the Office Roster table) and then c...