469,128 Members | 1,611 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

Having trouble with GROUP BY ...

SELECT TOP 25 *
FROM new_ticket
WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
ORDER BY group_num DESC


I am using the above method to get pagination with PHP Script.
It is running on MS SQL Server 2005 Express Edition.

After long been trying, I haven't found a way to get them grouped.
I modified very last line "GROUP BY group_num" and didn't work!

Is anyway this can be grouped and arrayed DESC order?
I need someone very Smart and save me!!
Sep 13 '07 #1
7 2193
ck9663
2,878 Expert 2GB
SELECT TOP 25 *
FROM new_ticket
WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
ORDER BY group_num DESC


I am using the above method to get pagination with PHP Script.
It is running on MS SQL Server 2005 Express Edition.

After long been trying, I haven't found a way to get them grouped.
I modified very last line "GROUP BY group_num" and didn't work!

Is anyway this can be grouped and arrayed DESC order?
I need someone very Smart and save me!!

am not a very smart but i can try to help you...

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 25 group_num
  2. FROM new_ticket 
  3. WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC) 
  4. GROUP BY group_num 
  5. ORDER BY group_num
  6.  
that is, if group_num is all you need. as a rule of thumb, you have to include in GROUP BY all non-aggreate functions in your select list
Sep 14 '07 #2
Thanks for your help. But, that's not exactly what I want.
There are more columns other than group_num and I'd like to display all other colunms based on the group_num displayed.

What I want to see is that other columns lined up according to the group numbers resulted from your SQL query.

Thanks



am not a very smart but i can try to help you...

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 25 group_num
  2. FROM new_ticket 
  3. WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC) 
  4. GROUP BY group_num 
  5. ORDER BY group_num
  6.  
that is, if group_num is all you need. as a rule of thumb, you have to include in GROUP BY all non-aggreate functions in your select list
Sep 14 '07 #3
ck9663
2,878 Expert 2GB
Thanks for your help. But, that's not exactly what I want.
There are more columns other than group_num and I'd like to display all other colunms based on the group_num displayed.

What I want to see is that other columns lined up according to the group numbers resulted from your SQL query.

Thanks

if you don't have duplicate group_num then this would be it

Expand|Select|Wrap|Line Numbers
  1.       SELECT TOP 25 *
  2.       FROM new_ticket
  3.       WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
  4.       ORDER BY group_num
Sep 14 '07 #4
Your worst guess was right. There are duplicate group_nums
I tried JOIN but didn't work.

id , group_num , issue
1 , 1 , A
------------------------------------------------------
2 , 1 , B
------------------------------------------------------
3 , 2 , C
------------------------------------------------------
4 , 2 , D
------------------------------------------------------



I want to see somthing like this.
id , group_num , issue
-------------------------------------
1 , 1 , A
--------------------------------------
3 , 2 , C
-------------------------------------



if you don't have duplicate group_num then this would be it

Expand|Select|Wrap|Line Numbers
  1.       SELECT TOP 25 *
  2.       FROM new_ticket
  3.       WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
  4.       ORDER BY group_num
Sep 14 '07 #5
ck9663
2,878 Expert 2GB
Your worst guess was right. There are duplicate group_nums
I tried JOIN but didn't work.

id , group_num , issue
1 , 1 , A
------------------------------------------------------
2 , 1 , B
------------------------------------------------------
3 , 2 , C
------------------------------------------------------
4 , 2 , D
------------------------------------------------------



I want to see somthing like this.
id , group_num , issue
-------------------------------------
1 , 1 , A
--------------------------------------
3 , 2 , C
-------------------------------------

what's the factor to consider 1,1,A instead of 1,1,B ? that would affect your query. if this is a troubleshooting ticket table, are you planning to get the first record or the latest one?
Sep 14 '07 #6
I am migrating MYSQL to MS-SQL. MYSQL script works fine
when use * with GROUP BY group_num and it shows all fileds in each row.
but Mssql doesn't support SELECT * and GROUP BY one_column.

id , group_num , issue, customer_name
1 , [1] , Wiper , john
------------------------------------------------------
2 , [3], Transmission , bill
------------------------------------------------------
3 , [3] , Engine , mike
------------------------------------------------------
4 , [3] , Engine , david
------------------------------------------------------


I want to see somthing like this.
As long as group_num shows only unique number, that will eliminate my headaches. The 2nd row could be 2 ,[3] Engine, david. As long as only one group_num appears on the list page that will be okay.

1 , [1] , Wiper , john
------------------------------------------------------
2 , [3] , Transmission , bill
------------------------------------------------------



what's the factor to consider 1,1,A instead of 1,1,B ? that would affect your query. if this is a troubleshooting ticket table, are you planning to get the first record or the latest one?
Sep 15 '07 #7
ck9663
2,878 Expert 2GB
I am migrating MYSQL to MS-SQL. MYSQL script works fine
when use * with GROUP BY group_num and it shows all fileds in each row.
but Mssql doesn't support SELECT * and GROUP BY one_column.

id , group_num , issue, customer_name
1 , [1] , Wiper , john
------------------------------------------------------
2 , [3], Transmission , bill
------------------------------------------------------
3 , [3] , Engine , mike
------------------------------------------------------
4 , [3] , Engine , david
------------------------------------------------------


I want to see somthing like this.
As long as group_num shows only unique number, that will eliminate my headaches. The 2nd row could be 2 ,[3] Engine, david. As long as only one group_num appears on the list page that will be okay.

1 , [1] , Wiper , john
------------------------------------------------------
2 , [3] , Transmission , bill
------------------------------------------------------
how's this:

Expand|Select|Wrap|Line Numbers
  1. select * from new_ticket a inner join 
  2. (select group_num, max(id) as id from new_ticket group by group_num) b on a.group_num = b.group_num and a.id = b.id
  3. where <that condition you have to check for the top 75% or something>
  4.  
Sep 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Jeremy Cowles | last post: by
7 posts views Thread by laclac01 | last post: by
10 posts views Thread by Alejandro Castañaza | last post: by
1 post views Thread by Hervé Piedvache | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.