How to select 1 entry without using "top 1" in SQL query?
-
I'm learning writing SQL query now. If I, for example, want to retrieve information from 3 joining tables (T1, T2, T3), and only want to return the latest failed task and task name is 'Mailboxes', I wrote the following query: select top 1 TT.task_type_desc, TL.starting_time, AAC.type_code_value from tasklog as TL inner join task_type as TT on TL.task_type_id=TT.task_type_id inner join aatype_code as AAC on AAC.type_code_id=TL.task_status and AAC.type_code_class='task_status' where AAC.type_code_value='Failed' and TT.task_type_desc='Mailboxes' order by TL.starting_time desc It gave me the required result. But how to do this query without using "top 1" and still get the same result? Thanks!
-
I'm learning writing SQL query now. If I, for example, want to retrieve information from 3 joining tables (T1, T2, T3), and only want to return the latest failed task and task name is 'Mailboxes', I wrote the following query: select top 1 TT.task_type_desc, TL.starting_time, AAC.type_code_value from tasklog as TL inner join task_type as TT on TL.task_type_id=TT.task_type_id inner join aatype_code as AAC on AAC.type_code_id=TL.task_status and AAC.type_code_class='task_status' where AAC.type_code_value='Failed' and TT.task_type_desc='Mailboxes' order by TL.starting_time desc It gave me the required result. But how to do this query without using "top 1" and still get the same result? Thanks!
If it ain't broke... why do you want to fix it? Paul
-
If it ain't broke... why do you want to fix it? Paul
Because, this is just part of my query, if I use top 1, then, I can't get other entries required by some other conditions. So, basically, on that part I mentioned in my previous mail, I just want the last failed entry with any task name. Any suggestions?
-
Because, this is just part of my query, if I use top 1, then, I can't get other entries required by some other conditions. So, basically, on that part I mentioned in my previous mail, I just want the last failed entry with any task name. Any suggestions?
Can't really answer without seeing the rest of your query, but you may be able to achieve what you're trying with a subquery:- select a.a,b.b,c.c from table_a a (nolock) inner join table_b b (nolock) inner join ( select top 1 * from table_c (nolock)) c Signature space for rent. Apply by email to....
-
Because, this is just part of my query, if I use top 1, then, I can't get other entries required by some other conditions. So, basically, on that part I mentioned in my previous mail, I just want the last failed entry with any task name. Any suggestions?
Anonymous wrote: Because, this is just part of my query, if I use top 1, then, I can't get other entries required by some other conditions. Use the
Top 1, you can use theUNIONstatement to make multiple selects like this to match all of your criteria:SELECT a, b, c, d FROM table_one WHERE a = 5
UNION
SELECT a, b, c, e FROM table_one WHERE a = 6
UNION
SELECT a, b, c, f FROM table_one WHERE a = 7
UNION
SELECT a, b, c, g FROM table_one WHERE a = 8
HTH :) Nick Parker
**The goal of Computer Science is to build something that will last at least until we've finished building it. - Unknown
**