|
Home > Archive > MS SQL Server > May 2005 > Return only first record found
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Return only first record found
|
|
|
| Just wondering if there is a way to return only the first record found, for
example I have a table like
****table1**********
**
* int first * int sec *
********************
* 1 * 4 *
* 1 * 5 *
* 2 * 2 *
* 5 * 1 *
* 5 * 6 *
* 5 * 8 *
********************
select table1.sec FROM table1 where table1.first = 5
this will return 1,6,8 but only want to return 1-the first one found.
thanks.
--
Paul G
Software engineer.
| |
| Jerry Spivey 2005-05-25, 8:23 pm |
| You can use the SET ROWCOUNT statement or preferably the TOP clause of the
SELECT statement i.e., TOP 1.
HTH
J
"Paul" <Paul@discussions.microsoft.com> wrote in message
news:26351589-A10C-41C1-8B53- 4FF111F5FBBA@microso
ft.com...
> Just wondering if there is a way to return only the first record found,
> for
> example I have a table like
> ****table1**********
**
> * int first * int sec *
> ********************
> * 1 * 4 *
> * 1 * 5 *
> * 2 * 2 *
> * 5 * 1 *
> * 5 * 6 *
> * 5 * 8 *
> ********************
> select table1.sec FROM table1 where table1.first = 5
> this will return 1,6,8 but only want to return 1-the first one found.
> thanks.
> --
> Paul G
> Software engineer.
| |
| David Portas 2005-05-25, 8:23 pm |
| SELECT MIN(sec)
FROM table1
WHERE first = 5
--
David Portas
SQL Server MVP
--
| |
|
| ok thanks this is what I was looking for
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> You can use the SET ROWCOUNT statement or preferably the TOP clause of the
> SELECT statement i.e., TOP 1.
>
> HTH
>
> J
> "Paul" <Paul@discussions.microsoft.com> wrote in message
> news:26351589-A10C-41C1-8B53- 4FF111F5FBBA@microso
ft.com...
>
>
>
| |
|
| Thanks for the response, unfortunately my table structure to not quite so
simple so I need another solution, possibly combining multiple queries in a
stored procedure.
Here is a simplified version of the table layout.
table 1
********************
********************
*
* pri key item * string name * datetime (entrydate) *
********************
********************
*
* 1 * item1 * 1/1/2005 *
* 2 * item2 * 5/1/2005 *
********************
********************
**
table 2
********************
*******
*int item * int destination # *
* 1 * 2 *
* 1 * 3 *
* 1 * 4 *
********************
********
I have built one query that returns records based on a date range, looking
at the entrydate from table 1. I need a second query or need to modify the
first to return only the first destination # from table 2.
a typical output from the first query if within the date range is, but more
records
item name entrydate
1 item1 1/1/2005
2 item2 5/1/2005
and I need (only the first destination)
item name entrydate destination #
1 item1 1/1/2005 2
2 item2 5/1/2005 some number
If I use TOP 1 in the first query I only get
item name entrydate
1 item1 1/1/2005
thanks.
--
Paul G
Software engineer.
"David Portas" wrote:
> SELECT MIN(sec)
> FROM table1
> WHERE first = 5
>
> --
> David Portas
> SQL Server MVP
> --
>
>
>
| |
| Mike Hodgson 2005-05-25, 8:23 pm |
| Do you mean 1 arbitrary destination or the minimum destination?
For 1 arbitrary destination:
select
t1.item,
t1.name,
t1.entrydate,
(select top 1 t2.destination from table2 as t2 where t1.item =
t2.item)
from table1 as t1
-- or alternately...
select t1.item, t1.name, t1.entrydate, t2.destination
from table1 as t1
left join table2 as t2 on t1.item = t2.item
where t2.destination is null
or t2.destination = (select top 1 destination from table2 as x where
x.item = t1.item)
For the minimum destination:
select
t1.item,
t1.name,
t1.entrydate,
(select min(t2.destination) from table2 as t2 where t1.item =
t2.item)
from table1 as t1
-- or alternately...
select t1.item, t1.name, t1.entrydate, t2.destination
from table1 as t1
left join table2 as t2 on t1.item = t2.item
where t2.destination is null
or t2.destination = (select min(destination) from table2 as x where
x.item = t1.item)
The different variations (with the subquery in the select list and where
clause respectively) will have different execution plans and different
IO/CPU costs. Which is more efficient will depend on index structures,
# rows in the tables, etc., etc. Trial & error is the best way to
determine this; anyway, you've got a couple options to play with.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
Paul wrote:
>Thanks for the response, unfortunately my table structure to not quite so
>simple so I need another solution, possibly combining multiple queries in a
>stored procedure.
>Here is a simplified version of the table layout.
> table 1
> ********************
********************
*
>* pri key item * string name * datetime (entrydate) *
> ********************
********************
*
>* 1 * item1 * 1/1/2005 *
>* 2 * item2 * 5/1/2005 *
> ********************
********************
**
>table 2
> ********************
*******
>*int item * int destination # *
>* 1 * 2 *
>* 1 * 3 *
>* 1 * 4 *
> ********************
********
>I have built one query that returns records based on a date range, looking
>at the entrydate from table 1. I need a second query or need to modify the
>first to return only the first destination # from table 2.
>a typical output from the first query if within the date range is, but more
>records
>item name entrydate
>1 item1 1/1/2005
>2 item2 5/1/2005
>and I need (only the first destination)
>item name entrydate destination #
>1 item1 1/1/2005 2
>2 item2 5/1/2005 some number
>If I use TOP 1 in the first query I only get
>item name entrydate
>1 item1 1/1/2005
>thanks.
>
>
| |
|
| Hi thanks for the response. I will give this a try, the first destination #
that it finds is sufficient.
--
Paul G
Software engineer.
"Mike Hodgson" wrote:
[color=darkred]
> Do you mean 1 arbitrary destination or the minimum destination?
>
> For 1 arbitrary destination:
>
> select
> t1.item,
> t1.name,
> t1.entrydate,
> (select top 1 t2.destination from table2 as t2 where t1.item =
> t2.item)
> from table1 as t1
>
> -- or alternately...
>
> select t1.item, t1.name, t1.entrydate, t2.destination
> from table1 as t1
> left join table2 as t2 on t1.item = t2.item
> where t2.destination is null
> or t2.destination = (select top 1 destination from table2 as x where
> x.item = t1.item)
>
>
> For the minimum destination:
>
> select
> t1.item,
> t1.name,
> t1.entrydate,
> (select min(t2.destination) from table2 as t2 where t1.item =
> t2.item)
> from table1 as t1
>
> -- or alternately...
>
> select t1.item, t1.name, t1.entrydate, t2.destination
> from table1 as t1
> left join table2 as t2 on t1.item = t2.item
> where t2.destination is null
> or t2.destination = (select min(destination) from table2 as x where
> x.item = t1.item)
>
> The different variations (with the subquery in the select list and where
> clause respectively) will have different execution plans and different
> IO/CPU costs. Which is more efficient will depend on index structures,
> # rows in the tables, etc., etc. Trial & error is the best way to
> determine this; anyway, you've got a couple options to play with.
>
> HTH
>
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
>
>
>
> Paul wrote:
>
| |
|
| Hi I almost have it working except where there is no item in table 2, it does
not return a record but I still need it to, kind of like it is missing a left
outer join somewhere.
I am using -without aliases and the real name of my tables
dbo. DML$Data_Item_Log_De
stination_T.Destination_ID =
(SELECT min (Destination_ID)FROM
dbo. DML$Data_Item_Log_De
stination_T AS x WHERE
x.Data_Item_Log_ID =
dbo.DML$Data_Item_Log_T.Data_Item_Log_ID)--
Thanks,
Paul G
Software engineer.
"Mike Hodgson" wrote:
[color=darkred]
> Do you mean 1 arbitrary destination or the minimum destination?
>
> For 1 arbitrary destination:
>
> select
> t1.item,
> t1.name,
> t1.entrydate,
> (select top 1 t2.destination from table2 as t2 where t1.item =
> t2.item)
> from table1 as t1
>
> -- or alternately...
>
> select t1.item, t1.name, t1.entrydate, t2.destination
> from table1 as t1
> left join table2 as t2 on t1.item = t2.item
> where t2.destination is null
> or t2.destination = (select top 1 destination from table2 as x where
> x.item = t1.item)
>
>
> For the minimum destination:
>
> select
> t1.item,
> t1.name,
> t1.entrydate,
> (select min(t2.destination) from table2 as t2 where t1.item =
> t2.item)
> from table1 as t1
>
> -- or alternately...
>
> select t1.item, t1.name, t1.entrydate, t2.destination
> from table1 as t1
> left join table2 as t2 on t1.item = t2.item
> where t2.destination is null
> or t2.destination = (select min(destination) from table2 as x where
> x.item = t1.item)
>
> The different variations (with the subquery in the select list and where
> clause respectively) will have different execution plans and different
> IO/CPU costs. Which is more efficient will depend on index structures,
> # rows in the tables, etc., etc. Trial & error is the best way to
> determine this; anyway, you've got a couple options to play with.
>
> HTH
>
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
>
>
>
> Paul wrote:
>
| |
| Mike Hodgson 2005-05-26, 8:23 pm |
| It's hard to tell what's going on just from this small snippet of code
you included. The 4 example queries I posted all worked exactly the
same (with the exception of the TOP vs MIN() bits) and I tested them
with the scenario you just mentioned. For the case where there is no
matching row in table2 for the row in table1, it included a NULL in the
resultset for the "destination" column (whether it was using the
subquery in the select list or the subquery in the where clause with the
left join). If you want some hard-coded data returned for that column
instead of NULL you can just wrap that column in an ISNULL() function.
Why don't you post your whole query (sanitize it if you wish - find &
replace tablename for a tablealias is a quick & easy way) and then we
can take a look and point out where it has gone wrong?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
Paul wrote:
[color=darkred]
>Hi I almost have it working except where there is no item in table 2, it does
>not return a record but I still need it to, kind of like it is missing a left
>outer join somewhere.
>I am using -without aliases and the real name of my tables
> dbo. DML$Data_Item_Log_De
stination_T.Destination_ID =
> (SELECT min (Destination_ID)FROM
>dbo. DML$Data_Item_Log_De
stination_T AS x WHERE
> x.Data_Item_Log_ID =
>dbo.DML$Data_Item_Log_T.Data_Item_Log_ID)--
>Thanks,
>Paul G
>Software engineer.
>
>
>"Mike Hodgson" wrote:
>
>
>
| |
|
| Hi thanks for the response, figured it may be difficult to tell just from the
small section of code. I have included the entire query below, 7 tables in
all. I also have not used aliases much but did rename the tables-T1-T7 to
make it more readable.
Hopefully it is possible to diagnose without knowing the table relationships.
SELECT TOP 100 PERCENT T1.Data_Item_Name_VC, T2.Event_Time_DT,
T3.POC_Name_VC,
T6.Originator_Name_VC, T2.Description_VC,
T2.JT3_Doc_Num_VC,
T4.Arrive_Depart_VC, T1.Category_ID,
T7.Destination_Name_VC
FROM T2 INNER JOIN
T4 ON T2.Arrive_Depart_ID = T4.Arrive_Depart_ID INNER
JOIN
T5 ON
T2.Data_Item_Log_ID = T5.Data_Item_Log_ID INNER JOIN
T7 ON T5.Destination_ID = T7.Destination_ID LEFT OUTER
JOIN
T1 ON T2.Data_Item_ID = T1.Data_Item_ID LEFT OUTER JOIN
T6 ON T2.Originator_ID = T6.Originator_ID LEFT OUTER
JOIN
T3 ON T2.POC_ID = T3.POC_ID
WHERE (T2.Last_Event_ID = 5) AND (T2.Deleted_BT <> 1) AND
(T2.Event_Time_DT >= CONVERT(DATETIME, @beg_date,
102)) AND
(T2.Event_Time_DT <= CONVERT(DATETIME, @end_date,
102))AND
T5.Destination_ID =
(SELECT TOP 1 (Destination_ID)FROM
T5 AS x WHERE
x.Data_Item_Log_ID = T2.Data_Item_Log_ID)
ORDER BY T1.Category_ID
Again this works except if there is no Data_Item_Log_ID in table T5, no
record is returned, where I need the record returned and just NULL for the
field.
Thanks,--
Paul G
Software engineer.
"Mike Hodgson" wrote:
> It's hard to tell what's going on just from this small snippet of code
> you included. The 4 example queries I posted all worked exactly the
> same (with the exception of the TOP vs MIN() bits) and I tested them
> with the scenario you just mentioned. For the case where there is no
> matching row in table2 for the row in table1, it included a NULL in the
> resultset for the "destination" column (whether it was using the
> subquery in the select list or the subquery in the where clause with the
> left join). If you want some hard-coded data returned for that column
> instead of NULL you can just wrap that column in an ISNULL() function.
>
> Why don't you post your whole query (sanitize it if you wish - find &
> replace tablename for a tablealias is a quick & easy way) and then we
> can take a look and point out where it has gone wrong?
>
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
>
>
>
> Paul wrote:
>
>
| |
| Hugo Kornelis 2005-05-27, 8:23 pm |
| On Fri, 27 May 2005 08:02:02 -0700, Paul wrote:
>Hi thanks for the response, figured it may be difficult to tell just from the
>small section of code. I have included the entire query below, 7 tables in
>all. I also have not used aliases much but did rename the tables-T1-T7 to
>make it more readable.
>Hopefully it is possible to diagnose without knowing the table relationships.
Hi Paul,
Not really. I can take a shot, but no guarantees at all. Table
structure, sample data and expected output really make this much easier,
you know!
(snip query)
>Again this works except if there is no Data_Item_Log_ID in table T5, no
>record is returned, where I need the record returned and just NULL for the
>field.
So you'll have to use an outer join for T5 instead of the current inner
join. And to make sure that it's not transformed back to an inner join,
all filter conditions for T5 have to go to the JOIN clause, and the join
of T5 to T7 has to be enclosed in parentheses (*) to enforce that this
join is carried out BEFORE the outer join:
(*) Note: actually, just reshuffling the order of the JOIN and ON
phrases suffices - the parentheses are just so that humans are able to
understand it as well. The indentation should help as well.
After removing the totally unneeded TOP 100 PERCENT, reformatting and
making the changes above, your query looks like this:
SELECT T1.Data_Item_Name_VC, T2.Event_Time_DT,
T3.POC_Name_VC, T6.Originator_Name_VC,
T2.Description_VC, T2.JT3_Doc_Num_VC,
T4.Arrive_Depart_VC, T1.Category_ID,
T7.Destination_Name_VC
FROM T2
INNER JOIN T4
ON T4.Arrive_Depart_ID = T2.Arrive_Depart_ID
LEFT OUTER JOIN (T5
INNER JOIN T7
ON T7.Destination_ID = T5.Destination_ID)
ON T5.Data_Item_Log_ID = T2.Data_Item_Log_ID
AND T5.Destination_ID =
(SELECT TOP 1 (Destination_ID)
FROM T5 AS x
WHERE x.Data_Item_Log_ID = T2.Data_Item_Log_ID)
LEFT OUTER JOIN T1
ON T1.Data_Item_ID = T2.Data_Item_ID
LEFT OUTER JOIN T6
ON T6.Originator_ID = T2.Originator_ID
LEFT OUTER JOIN T3
ON T3.POC_ID = T2.POC_ID
WHERE T2.Last_Event_ID = 5
AND T2.Deleted_BT <> 1
AND T2.Event_Time_DT >= CONVERT(DATETIME, @beg_date, 102)
AND T2.Event_Time_DT <= CONVERT(DATETIME, @end_date, 102)
ORDER BY T1.Category_ID
(untested, since you didn't provide CREATE TABLE and INSERT statements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
| |
|
| Hi Hugo, thanks for the information, just tested it out in query analyzer and
it works!
I used VS.net view in server explorer to create the query and noticed it
automatically put in the TOP 100 PERCENT .
--
Paul G
Software engineer.
"Hugo Kornelis" wrote:
> On Fri, 27 May 2005 08:02:02 -0700, Paul wrote:
>
>
> Hi Paul,
>
> Not really. I can take a shot, but no guarantees at all. Table
> structure, sample data and expected output really make this much easier,
> you know!
>
> (snip query)
>
> So you'll have to use an outer join for T5 instead of the current inner
> join. And to make sure that it's not transformed back to an inner join,
> all filter conditions for T5 have to go to the JOIN clause, and the join
> of T5 to T7 has to be enclosed in parentheses (*) to enforce that this
> join is carried out BEFORE the outer join:
>
> (*) Note: actually, just reshuffling the order of the JOIN and ON
> phrases suffices - the parentheses are just so that humans are able to
> understand it as well. The indentation should help as well.
>
> After removing the totally unneeded TOP 100 PERCENT, reformatting and
> making the changes above, your query looks like this:
>
> SELECT T1.Data_Item_Name_VC, T2.Event_Time_DT,
> T3.POC_Name_VC, T6.Originator_Name_VC,
> T2.Description_VC, T2.JT3_Doc_Num_VC,
> T4.Arrive_Depart_VC, T1.Category_ID,
> T7.Destination_Name_VC
> FROM T2
> INNER JOIN T4
> ON T4.Arrive_Depart_ID = T2.Arrive_Depart_ID
> LEFT OUTER JOIN (T5
> INNER JOIN T7
> ON T7.Destination_ID = T5.Destination_ID)
> ON T5.Data_Item_Log_ID = T2.Data_Item_Log_ID
> AND T5.Destination_ID =
> (SELECT TOP 1 (Destination_ID)
> FROM T5 AS x
> WHERE x.Data_Item_Log_ID = T2.Data_Item_Log_ID)
> LEFT OUTER JOIN T1
> ON T1.Data_Item_ID = T2.Data_Item_ID
> LEFT OUTER JOIN T6
> ON T6.Originator_ID = T2.Originator_ID
> LEFT OUTER JOIN T3
> ON T3.POC_ID = T2.POC_ID
> WHERE T2.Last_Event_ID = 5
> AND T2.Deleted_BT <> 1
> AND T2.Event_Time_DT >= CONVERT(DATETIME, @beg_date, 102)
> AND T2.Event_Time_DT <= CONVERT(DATETIME, @end_date, 102)
> ORDER BY T1.Category_ID
>
> (untested, since you didn't provide CREATE TABLE and INSERT statements)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
| |
| Hugo Kornelis 2005-05-27, 8:23 pm |
| On Fri, 27 May 2005 15:39:20 -0700, Paul wrote:
>Hi Hugo, thanks for the information, just tested it out in query analyzer and
>it works!
>I used VS.net view in server explorer to create the query and noticed it
>automatically put in the TOP 100 PERCENT .
Hi Paul,
Silly tools.
Anyway, if you can, I suggest to use Query Analyzer instead. If only for
the much bigger editing window (but that is actually one of the minor
advantages... see http://www.aspfaq.com/show.asp?id=2455
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
| |
|
| thanks for the additional information. I generally do use Query analyzer for
developing queries but am still kind of new so for the more complicated
queries will sometimes use the view tool in vs.net server explorer.
--
Paul G
Software engineer.
"Hugo Kornelis" wrote:
> On Fri, 27 May 2005 15:39:20 -0700, Paul wrote:
>
>
> Hi Paul,
>
> Silly tools.
>
> Anyway, if you can, I suggest to use Query Analyzer instead. If only for
> the much bigger editing window (but that is actually one of the minor
> advantages... see http://www.aspfaq.com/show.asp?id=2455
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|
|
|
|
|