Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

problem in select command Expand / Collapse
Author
Message
Posted Saturday, March 16, 2013 5:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 8:47 PM
Points: 18, Visits: 22
hi
please look at this select command :
select top 2 * from dbo.NFFeeds order by id desc 

for my database the result is 2 rows .first by id=33 and second by id=32
by this command i get two last rows from my table . how can i get this two rows like this :
first row by id=32 and second row by id=33
Post #1431950
Posted Saturday, March 16, 2013 6:59 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
I'm going to assume there's more rows in the table than 2, and you want just the last 2 of them ordered in ascending order?

Try:
;WITH MyData AS (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC
)
SELECT * FROM MyData
ORDER BY id



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1431954
Posted Sunday, March 17, 2013 2:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 8:47 PM
Points: 18, Visits: 22
MyDoggieJessie (3/16/2013)
I'm going to assume there's more rows in the table than 2, and you want just the last 2 of them ordered in ascending order?

Try:
;WITH MyData AS (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC
)
SELECT * FROM MyData
ORDER BY id


thanks dear 'MyDoggieJessie ' ... it's that i want.
Post #1431962
Posted Sunday, March 17, 2013 6:07 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
You could also do it without cte. Why employ complex technique when you can use simple one? Cte is really great for recursion and multiple use of the same subquery, but here is not necessary. Plain-simple inline view will do.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1432051
Posted Sunday, March 17, 2013 6:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 20,859, Visits: 32,882
Vedran Kesegic (3/17/2013)
You could also do it without cte. Why employ complex technique when you can use simple one? Cte is really great for recursion and multiple use of the same subquery, but here is not necessary. Plain-simple inline view will do.


Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432053
Posted Sunday, March 17, 2013 6:53 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
Lynn Pettis (3/17/2013)
Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.



Let's compare:
;WITH MyData AS (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC
)
SELECT * FROM MyData
ORDER BY id

VS.
SELECT * FROM (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC) MyData
ORDER BY id

Derived table wins as it does not contain extra words ";WITH MyData AS ".

Apparently, CTE's are more complex comparing to derived tables.
Post #1432055
Posted Sunday, March 17, 2013 7:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 20,859, Visits: 32,882
Sergiy (3/17/2013)
Lynn Pettis (3/17/2013)
Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.



Let's compare:
;WITH MyData AS (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC
)
SELECT * FROM MyData
ORDER BY id

VS.
SELECT * FROM (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC) MyData
ORDER BY id

Derived table wins as it does not contain extra words ";WITH MyData AS ".

Apparently, CTE's are more complex comparing to derived tables.


I will have to disagree with you. You are saying having to type "WITH MyData as " makes the query complex. Not the case. Take a look at the following actual execution plan and tell me if one is more complex than other.

Here is the quick DDL and DML I used to create the execution plans:


create table dbo.NFFeeds (
id int,
datacol varchar(10));

insert into dbo.NFFeeds
values (1,'A'),(2,'B'),(3,'C');





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)


  Post Attachments 
CTEvsDerivedTable.sqlplan (8 views, 21.29 KB)
Post #1432058
Posted Sunday, March 17, 2013 7:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
The plans looks identical.
No difference here.

So, the oonly difference left is in extra wording you need to write every time when you use CTE instead of a simple derived table.

Post #1432060
Posted Sunday, March 17, 2013 7:28 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 20,859, Visits: 32,882
So, does the extra typing make it more complex? I think it makes the query more understandable.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432061
Posted Sunday, March 17, 2013 7:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
Lynn Pettis (3/17/2013)
So, does the extra typing make it more complex? I think it makes the query more understandable.

Extra typing always makes anything more complex.
By definition.

More typing - more reading - longer parsing.
Even if it's insignificant by your opinion - it's still more, not less.

As for "more understandable" - it's totally a matter of habit.
For me - it's another way around.
Especially when it comes to multi-layer DT's.

But if to ingnore our personal preferences - the query written using DT is simpler than the same query written using CTE.

Post #1432065
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse