Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 22nd, 2006, 01:43 PM
Authorized User
 
Join Date: Sep 2006
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How do I get it to pull only one name even if the number of calls are the same.

Alexander Nelson
Programmer
 
Old September 22nd, 2006, 01:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sorry about that missing closing parentheses.

So, if there were more than one row with the same minimum value, which one do you want?

(Be careful with your answer, I'm laying a trap. :D)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old September 22nd, 2006, 02:29 PM
Authorized User
 
Join Date: Sep 2006
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The first one!!!

Alexander Nelson
Programmer
 
Old September 23rd, 2006, 05:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

...and the jaws spring shut ...

Let's assume that there are a number of rows all with the same number of (minimum) calls. These rows together form a set.

When you say you want "The first one" from this set, the word "first" is an ordinal number. This implies that the set is ordered in some way. If there is no ordering, then what do you mean by "first"? If there is no ordering, then any row at all could be first, last, or anything in between. Actually, if there is no ordering, then such concepts as "first", "last", or "twenty-seventh" have no meaning at all.

Indeed, in SQL, the rows which form the resultset from a query, absent any ORDER BY clause, are an unordered set, and the query processor is free to return that resultset in any order it wants, or rather, in no order at all. You must not make any assumptions about the order of a resultset unless you have specifically indicated you want an ordering via the ORDER BY clause.

When you say "first", you are (probably) referring to the row which "comes before" all the other rows. In order to determine that, there must be something which determines the "come-before-ing" property of a row. Typically, this sort of thing would be a date, such as a "LastActivityDate", or maybe "LastCallDate".

Now, if you have such a column in your rows, you could order the resultset from the minimum query we've discussed by that column, then use the TOP 1 qualifier to extract the "first" row as ordered by that date column.

Thus, you'll need to have some value which can be used to order the resultset. Once you ORDER BY that column value, then such concepts as "first" and "last" have a meaning.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
How does one get the Max or Min Filename? crabjoe ASP.NET 1.0 and 1.1 Basics 4 January 15th, 2008 04:48 PM
MAX/MIN Value Help slbibs SQL Server 2005 4 November 6th, 2007 04:00 AM
Min / Max followup danbush XSLT 2 August 31st, 2005 12:02 PM
min-height in IE cgallagr98 CSS Cascading Style Sheets 5 April 8th, 2005 12:47 PM
MIN() function mohiddin52 Access 1 October 29th, 2004 07:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.