SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

I was called many times by Jr. Programmers in team to debug their SQL. I keep log of most of the problems and review them afterwards. This helps me to evaluate my team and identify most important next thing which I can do to improve the performance and productivity of it. Recently we have many new hires and they had almost similar questions. Since, I have send them following sequence of the SELECT clause I am not interrupted often, which helps me to focus on larger project architectural design.

SELECT yourcolumns
FROM tablenames
JOIN tablenames
WHERE condition
GROUP BY yourcolumns
HAVING aggregatecolumn condition
ORDER BY yourcolumns

Once above order is memorized it guides the basic construction of SELECT statement. This article is written keeping in mind beginners in SQL Server language and presents very higher level outline of SELECT statement.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

  1. How Can we generate incremented linear number in sql server
    as in oracle we genaerate in via sequence …

    I want the o/p like–

    1
    2
    3
    4
    5
    6
    7
    8
    9

    Like

  2. Pingback: SQL SERVER - Generate Incremented Linear Number Sequence Journey to SQL Authority with Pinal Dave

  3. Hi Ashutosh Gupta,

    Once we order something, we can not group them as the group which we desire may not be together. It only make sense to group them first and then order them. This way similar data will be grouped and only distinct data/grouped data is properly ordered.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

    Like

  4. select row_number() over(partition by gender order by empname) as Srno,* from Employees

    Row_Number Function have following syntax

    Row_Number() Over([Partition by columnName] Order by columnName)

    where Partition by is optional but Order by is compulsory.

    Like

  5. use This code you can get the serial order

    select row_number() over(partition by gender order by empname) as Srno,* from Employees

    Row_Number Function have following syntax

    Row_Number() Over([Partition by columnName] Order by columnName)

    where Partition by is optional but Order by is compulsory.

    Row_Number funciton returns sequence for each row based on Order by clause, if you mention partition by also row number will split rows based on partition by column and then generate the separate sequence numbers.

    Like

    • Hi nagender,
      please tell me what the means of serial order.
      Actually i ran you code using temp table
      Find below code and give me suggestion.
      Sorry but i am beginner.
      create table #emp(empName nvarchar(10),gender nvarchar(10))
      select * from #emp
      insert into #emp(empName,gender)values(‘xyz’,’male’)
      insert into #emp(empName,gender)values(‘mno’,’male’)
      insert into #emp(empName,gender)values(‘kld’,’female’)
      insert into #emp(empName,gender)values(‘clg’,’female’)
      select row_number() over(partition by empName order by gender) as Srno,* from #emp

      Output:-
      Srno empName gender
      1 clg female
      1 kld female
      1 mno male
      1 xyz male
      my question is in result not showing sequence no.

      Like

  6. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  7. Hi pinal,

    I have to prepare a SSIS package which takes data from a table say a and then transfers data between two tables say b and c based on information of table a.
    is it possible through SSIS?
    if it is please tell me how it can be done?

    Like

  8. It is also useful to know the order of evaluation in order to realise why, for example, column aliases are only usable in the ORDER BY clause and not the GROUP BY

    (5) SELECT (6) DISTINCT
    (1) FROM
    (2) WHERE
    (3) GROUP BY
    (4) HAVING
    (7) ORDER BY

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s