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 (https://blog.sqlauthority.com)

Database, DBA, SQL Scripts
Previous Post
SQL SERVER – Explanation SQL SERVER Hash Join
Next Post
SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases – Part 2

Related Posts

14 Comments. Leave new

  • Your site has won a Blog of the Day Award (BOTDA)

    Award Code

    Thank you,

    famous quotes

    Reply
  • Ashutosh Gupta
    October 31, 2007 3:18 am

    Hi Pinal

    I hav a question,
    Why we use group by clause before order by clause.

    Ashutosh

    Reply
  • Aniruddha Singh
    November 21, 2007 12:39 pm

    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

    Reply
  • 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 ( https://blog.sqlauthority.com/ )

    Reply
  • hi ,Pinal
    i didnt understand
    how to debugging the stored procedures in sqlserver2005
    Please give me answer

    thnaks & Regards

    Reply
  • Hi, pinal

    how to use searchable keyword in asp.net with sql server

    Please give me answer

    thnaks & Regards

    Reply
  • 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.

    Reply
  • 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.

    Reply
    • 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.

      Reply
  • Wooow,great solution nagender. thanks alot,a have been looking for this solution for long time ago. Nice job.

    Reply
  • 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?

    Reply
  • is this in right order:

    1. SELECT
    2. FROM
    3. JOIN
    4. ON
    5. WHERE
    6. GROUP BY
    7. HAVING
    8. ORDER BY

    Reply
  • 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

    Reply
  • I have this data

    ID Name State Check
    1 Raj UP ok
    1 Raj UP Notok
    2 bob MP ok
    2 bob MP ok
    3 joy MH Notok
    3 joy MH Notok
    I want following data

    ID Name State Check flag
    1 Raj UP ok y
    1 Raj UP Notok n
    2 bob MP ok y
    2 bob MP ok n
    3 joy MH Notok y
    3 joy MH Notok n

    Reply

Leave a Reply

Menu
Exit mobile version