March 16, 2013 at 5:53 pm
hi
please look at this select command :
[Code]select top 2 * from dbo.NFFeeds order by id desc [/code]
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
March 16, 2013 at 6:59 pm
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; Theyll drag you down to their level and beat you with experience
March 17, 2013 at 2:25 am
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.
March 17, 2013 at 6:07 pm
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.
March 17, 2013 at 6:41 pm
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.
March 17, 2013 at 6:53 pm
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.
😎
_____________
Code for TallyGenerator
March 17, 2013 at 7:09 pm
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');
March 17, 2013 at 7:21 pm
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.
😎
_____________
Code for TallyGenerator
March 17, 2013 at 7:28 pm
So, does the extra typing make it more complex? I think it makes the query more understandable.
March 17, 2013 at 7:43 pm
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.
😎
_____________
Code for TallyGenerator
March 18, 2013 at 1:02 am
Cte in some situations with really long DT can help readability, but this is not the case here. Some folks (ab)use cte in every statement because they don't know how to write DT. They do not have freedom (or knowledge) to choose between two. You have, and that's good, but original poster maybe would learn cte without knowing dt, and start to write everything as cte. Not because he decided he likes cte better, but because he doesn't know how to write regular dt, and thus cannot choose. It turns that many tsql beginners don't, unfortunately.
March 18, 2013 at 5:31 am
I find that derived tables make the queries more difficult to read and understand. Using CTEs allows you to move the derived table to an easy to understand construct just before the query and use it CTE name just like a table in the query simplifying the query.
March 18, 2013 at 5:58 am
...
Derived table wins as it does not contain extra words ";WITH MyData AS ".
...
:w00t::w00t::w00t::w00t::w00t:
"extra words" is a greatest argument to declare a "code" winner I'have ever seen.
Extra typing always makes anything more complex.
By definition.
There is no such definition! Actually, the opposite is quite more common.
Short size of code doesn't make it simpler and that is relevant not only to T-SQL but to many other programming languages.
From my experience, most of developers finding CTE is much more clear way to write query and therefore less complex then using "derived tables".
March 18, 2013 at 6:05 am
I really liked it, so my second response !
Sergiy (3/17/2013)
Lynn Pettis (3/17/2013)
Really, CTEs arecomplex? 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 want to be part of your competition. So, my version wins over yours one as it contains less number of characters:
SELECT * FROM (SELECT TOP 2 * FROM dbo.NFFeeds ORDER BY id DESC) m ORDER BY id
It wins as:
1. It has less New Line characters
2. It doesn't have tabs (or extra spaces) for indent
3. Most important one - my alias "m" is 7 (seven!) times shorter than yours one "MyData"!
May I have a medal now?
:hehe::hehe::hehe::hehe::hehe:
March 18, 2013 at 12:48 pm
Eugene Elutin (3/18/2013)
:w00t::w00t::w00t::w00t::w00t:"extra words" is a greatest argument to declare a "code" winner I'have ever seen.
Did you find anything else different?
Anything?
If you missed it - I responded on this statement:
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.
So, I compared those 2 ways of writing queries.
Having no other points of differece, less typing makes DT queries easier to write.
Extra typing always makes anything more complex.
By definition.
There is no such definition! Actually, the opposite is quite more common.
Opposite is stupid.
Smeh bez prichiny - priznak durachiny.
If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it (including SQL optimizer).
There must be a reason to add an extra line to a code.
Inline documentation, better formatting, whatever else - everytime there must be a reason.
I cannot see any advantage of that particular extra line.
Can you point on it?
From my experience, most of developers finding CTE is much more clear way to write query and therefore less complex then using "derived tables".
Most developers used to wrire "CSS style" code.
CTE fits there patterns and allows not to learn SQL-specific best practices.
That's why it's so popular.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply