Hard SQL

  • Thread starter Thread starter Carlos Durán Urenda
  • Start date Start date
C

Carlos Durán Urenda

Hi

This SQL returns to me the last 10 records in a table, order by Id field

SELECT * FROM MyTable WHERE Id IN ( SELECT TOP 10 Id FROM Table
ORDER BY Id DESC) ORDER BY Id

But i need to get a similar result based in two o more id Fields

can I to do it with the EXISTS Statment?, I found this example...
SELECT * FROM suppliers WHERE EXISTS (select * from orders where
suppliers.supplier_id = orders.supplier_id);

But how can i do to apply it to the same table?

Can I use an Alias?


Can someone say to me how to do it?

Tnks
CD
 
Carlos Durán Urenda said:
Hi

This SQL returns to me the last 10 records in a table, order by Id field

SELECT * FROM MyTable WHERE Id IN ( SELECT TOP 10 Id FROM Table
ORDER BY Id DESC) ORDER BY Id
Click to expand...

I apologize, but it really returns the rows from the outer table
where the column values equal the last 10 rows in the output
sort-order of the inner table, not the last ten rows in either table
in the query. There is a big difference.
But i need to get a similar result based in two o more id Fields

can I to do it with the EXISTS Statment?, I found this example...
SELECT * FROM suppliers WHERE EXISTS (select * from orders where
suppliers.supplier_id = orders.supplier_id);

But how can i do to apply it to the same table?
Click to expand...

Um. By naming the same table in the EXISTS predicate?

SELECT *
FROM suppliers AS S1
WHERE EXISTS
(select *
from suppliers AS S2
where S1.supplier_id = S2.supplier_id);

Not sure why you'd want to execute that query, but there it is.

Can I use an Alias?
Click to expand...

I highly recommend using Table aliases at all times, as it *vastly*
improves the readability of your SQL.
Can someone say to me how to do it?
Click to expand...

I apologize again, but I'm not 100% sure what the question is.
 
Chris2 said:
Um. By naming the same table in the EXISTS predicate?

SELECT *
FROM suppliers AS S1
WHERE EXISTS
(select *
from suppliers AS S2
where S1.supplier_id = S2.supplier_id);

Not sure why you'd want to execute that query
Click to expand...

Good suggestion but I think the OP wants a derived table rather than a
subquery e.g.

SELECT *
FROM Table AS T1
INNER JOIN (
(SELECT TOP 10 Id
FROM Table
ORDER BY Id DESC) AS DT1
ON T1.Id = DT1.Id;

Jamie.

--
 
tnks Jamie



Jamie Collins said:
Good suggestion but I think the OP wants a derived table rather than a
subquery e.g.

SELECT *
FROM Table AS T1
INNER JOIN (
(SELECT TOP 10 Id
FROM Table
ORDER BY Id DESC) AS DT1
ON T1.Id = DT1.Id;

Jamie.
Click to expand...
 
Back
Top