Grouping Result

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

I want my result rows to have an uniqe value in col_3. How can I exclude rows (but one) that have the same value in col_3?

Thanx

/sf

SELECT TOP 1 col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

|||Thank you for your answer.

But will not this query only output one row?

I'll try to explain my problem better:

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

What I want is output all rows with unique value in col_3 and if several rows have the same value in col_3, I only want one of those rows. The following could have worked, but it's not valid sql:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1
GROUP BY col_3|||SELECT MAX(col_1), MAX(col_2), col_3, MAX(col_4) FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY MAX(col_1)
GROUP BY col_3

I used MAX() for the non-grouped values, but you could use MIN(), SUM(), AVG()

Grouping order conditionally?!

Hi,
I have a matrix with some groups and data.
I am passing a parameter called "theSort" to the report which is either "T"
or "C"
Two groups are TITLE and COUNTRY
If "T" is passed I would like the matrix to put TITLE first (in the first
column in matrix) and then COUNTRY after it. If "C" is passed then vice versa.
Is this possible' It didn't seem to work when I tried the following:
In the matrix's group tab, I put expressions for each group like so...
For Title group i put:
=IIF(Parameters!theSort Is "T",Fields!Title.Value,Fields!Country.Value)
For Country group i did:
=IIF(Parameters!theSort Is "T",Fields!Country.Value,Fields!Title.Value)
Didnt work.
Any help appreciated.
regards
KSTry =IIF(Parameters!theSort.Value ="T",Fields!Title.Value,Fields!Country.Value)
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"saleek" <saleek@.discussions.microsoft.com> wrote in message
news:7787C07F-EF50-4E21-83C8-EA2E37DF3531@.microsoft.com...
> Hi,
> I have a matrix with some groups and data.
> I am passing a parameter called "theSort" to the report which is either
"T"
> or "C"
> Two groups are TITLE and COUNTRY
> If "T" is passed I would like the matrix to put TITLE first (in the first
> column in matrix) and then COUNTRY after it. If "C" is passed then vice
versa.
> Is this possible' It didn't seem to work when I tried the following:
> In the matrix's group tab, I put expressions for each group like so...
>
> For Title group i put:
> =IIF(Parameters!theSort Is "T",Fields!Title.Value,Fields!Country.Value)
> For Country group i did:
> =IIF(Parameters!theSort Is "T",Fields!Country.Value,Fields!Title.Value)
> Didnt work.
> Any help appreciated.
> regards
> KS

GROUP BY!

SELECT MIN(Salary) FROM (
SELECT TOP 10 Salary FROM tblEmp ORDER BY Salary DESC) AS X
As such the resultset of the above query will only have the record of
the Salary column. I want the record of another column named EmpName as
well in the resultset. So where do I accomodate the 'GROUP BY EmpName'
clause in the above query to get the EmpName in the resultset?
Thanks,
ArpanOne way to accomplish this:
SELECT
Salary,
EmpName
FROM tblEmp
WHERE Salary =
(
SELECT
MIN(Salary)
FROM
(
SELECT TOP 10
Salary
FROM tblEmp
ORDER BY
Salary DESC
) AS X
)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1130343229.752921.153920@.o13g2000cwo.googlegroups.com...
> SELECT MIN(Salary) FROM (
> SELECT TOP 10 Salary FROM tblEmp ORDER BY Salary DESC) AS X
> As such the resultset of the above query will only have the record of
> the Salary column. I want the record of another column named EmpName as
> well in the resultset. So where do I accomodate the 'GROUP BY EmpName'
> clause in the above query to get the EmpName in the resultset?
> Thanks,
> Arpan
>|||Thanks, Adam.
Regards,
Arpan

GROUP BY useage

My query:

SELECT TOP 10 stats_page
FROM site_stats
GROUP BY stats_page
ORDER BY idDESC

Produces the error:
Column "site_stats.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone please explain why this does not work? And perhaps offer a solution?

Thanks

From the error message we can see your query conflict with a rule of using ORDER BY clause in T-SQL. So you can write a query like this:

SELECT TOP 10 stats_page
FROM site_stats
GROUP BY stats_page,id
ORDER BY idDESC

|||

Because, you are asking for the top 10 results of stats_page.

It's akin to saying, ok, make me a list of the top 10 most expensive cars in the world. Now sort that list by color.

Each car can come in one of many colors (As each of your stats_page can have multiple id's), but what color (id) are you referring to for each car? Did you want to sort by the least color (alphabetically) that each car is available in? So if the ferrari is only available in green and red, it would come after a mercedes benz that is available in black, purple, and yellow (Since the least color for ferrari is green, and the least color for mercedes is black)? Or did you want to sort by the highest color (alphabetically) so that the ferrari (red) comes before mercedes (yellow)? Or sort by the number of colors each car is available in (2 for ferrari, 3 for mercedes)?

You must choose which ID is to represent each stats_page since there are multiple ID's for each stats_page (If there is not multiple, then you shouldn't be using GROUP BY). You could use an ORDER BY MIN(id) if you want to order by the lowest id for each stats_page, or ORDER BY MAX(id) if you want to order by the highest id, or COUNT(id) for the number of id's, etc.

|||

Iori_Jay:

From the error message we can see your queryconflict with a rule of using ORDER BY clause in T-SQL. So you canwrite a query like this:

SELECT TOP 10 stats_page
FROM site_stats
GROUP BY stats_page,id
ORDER BY idDESC

Even if I use that, I get a syntax error on the "GROUP BY stats_page,id" line.

Motley:

You must choose which ID is to represent each stats_page since there are multiple ID's for each stats_page (If there is not multiple, then you shouldn't be using GROUP BY). You could use an ORDER BY MIN(id) if you want to order by the lowest id for each stats_page, or ORDER BY MAX(id) if you want to order by the highest id, or COUNT(id) for the number of id's, etc.

soo...my new query would be...? Sorry but I'm a little lost here xD|||

lori_Jay:

Motley:

You must choose which ID is to represent each stats_page since there are multiple ID's for each stats_page (If there is not multiple, then you shouldn't be using GROUP BY). You could use an ORDER BY MIN(id) if you want to order by the lowest id for each stats_page, or ORDER BY MAX(id) if you want to order by the highest id, or COUNT(id) for the number of id's, etc.

soo...my new query would be...? Sorry but I'm a little lost here xD

Something like this:

SELECT TOP 10 stats_page, MIN(id)
FROM site_stats
GROUP BY stats_page
ORDER BY MIN(id)DESC

|||I would need a small sample of what your site_stats table data looks like, and what result you want the query to give in order to give you an answer. I'm guessing the query you want isn't really what you have, and I'm unsure of what it is that you want.|||Yay, I'm starting to make progress.

The query now works using:
SELECT TOP 10 stats_page, MIN(id)
FROM site_stats
GROUP BY stats_page
ORDER BY MIN(id) DESC

And to answer motley's question. The site_stats table contains:
id, stats_ip, stats_page, stats_date

All I want to be able to do (In this case) is to see the most frequently visited pages.
ie. I'd be expecting something like Default.aspx to be first as it's visited most frequently, followed by something like login.aspx. The amount of times the page has been accessed isn't really nesscary.|||SELECT TOP 10 stats_page,COUNT(*)
FROM site_stats
GROUP BY stats_page
ORDER BY COUNT(*) DESC|||That works perfectly.

Thanks every much for all your help :)

Group By Memo Field

I need to group by a memo field but it doesn't allow me, I tried making a formula in order to use it and I got a error Blob/Memo can't be used in formula...
I'm using CR 8.5
Please helptry using...

Insert -> Group

Choose the Memo Field... it will group by the memo field by then...

If this is not what you want you gotta give details...

Group by hour, month etc

Hi All,

I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries

1. Get number of orders per year

2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)

3. Get number of orders per hour

Note: If there are no orders in a year etc, it should return a row with 0.

You need to use the DatePart function in your SQL.|||

1.

select datepart(year, OrderTime), count(*) from table group by datepart(year, OrderTime)

or

select year(OrderTime), count(*) from table group by year(OrderTime)

or

select dateadd(year, datediff(year, 0, OrderTime), 0), count(*) fromtable group by dateadd(month, datediff(month, 0, OrderTime), 0)

2.

select dateadd(month, datediff(month, 0, OrderTime), 0), count(*) from table group by dateadd(month, datediff(month, 0, OrderTime), 0)

3.

select dateadd(hour, datediff(hour, 0, OrderTime), 0), count(*) from table group by dateadd(hour, datediff(hour, 0, OrderTime), 0)

|||

sachinsurana:

Note: If there are no orders in a year etc, it should return a row with 0.

No order for which year ? Year 2008, 2009, 3000 ? You have to define what are the range of year. If you have a calendar table, use it to LEFT JOIN to your table.

|||I dont have a calender table and I can't create one as we are not supposed to modify the database.|||

then create one on the fly

select [year]

from

(

select 2000 as [year] union all select 2001 as [year] union all select 2002 as [year]

) y

or

make use of this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

group by clause Query help

This is my query

select ano,max(date),a_subject from MY_TAB where table_name='xyz' and
ano=877
group by a_subject,ano order by a_subject

ANOmax(Date)A_Subject
8772005-01-20 00:00:00.000Subject_1
8771900-01-01 00:00:00.000Subject_2
8772004-12-20 00:00:00.000Subject_3
8772005-01-19 00:00:00.000Subject_4
-----------------------
When I put the status column in, it fetches all the rows.

select ano,max(date),a_subject,status from MY_TAB where
table_name='xyz' and ano=877 group by a_subject,ano,status order by
a_subject

ANOmax(Date)A_SubjectStatus
8772005-01-20 00:00:00.000Subject_1Not Started
8771900-01-01 00:00:00.000Subject_2Not Started
8772004-12-20 00:00:00.000Subject_3Completed
8771900-01-01 00:00:00.000Subject_3Not Started
8771900-01-01 00:00:00.000Subject_4Not Started
8772005-01-19 00:00:00.000Subject_4Not Started
----------------------
now what i want is

ANOmax(Date)A_SubjectStatus
8772005-01-20 00:00:00.000Subject_1Not Started
8771900-01-01 00:00:00.000Subject_2Not Started
8772004-12-20 00:00:00.000Subject_3Completed
8772005-01-19 00:00:00.000Subject_4Not Started
Thanks a lot for your help.

AJ[posted and mailed, please reply in news]

(aj70000@.hotmail.com) writes:
> select ano,max(date),a_subject from MY_TAB where table_name='xyz' and
> ano=877
> group by a_subject,ano order by a_subject
> ANO max(Date) A_Subject
> 877 2005-01-20 00:00:00.000 Subject_1
> 877 1900-01-01 00:00:00.000 Subject_2
> 877 2004-12-20 00:00:00.000 Subject_3
> 877 2005-01-19 00:00:00.000 Subject_4
> -----------------------
> When I put the status column in, it fetches all the rows.
> select ano,max(date),a_subject,status from MY_TAB where
> table_name='xyz' and ano=877 group by a_subject,ano,status order by
> a_subject
> ANO max(Date) A_Subject Status
> 877 2005-01-20 00:00:00.000 Subject_1 Not Started
> 877 1900-01-01 00:00:00.000 Subject_2 Not Started
> 877 2004-12-20 00:00:00.000 Subject_3 Completed
> 877 1900-01-01 00:00:00.000 Subject_3 Not Started
> 877 1900-01-01 00:00:00.000 Subject_4 Not Started
> 877 2005-01-19 00:00:00.000 Subject_4 Not Started
> ----------------------
> now what i want is
> ANO max(Date) A_Subject Status
> 877 2005-01-20 00:00:00.000 Subject_1 Not Started
> 877 1900-01-01 00:00:00.000 Subject_2 Not Started
> 877 2004-12-20 00:00:00.000 Subject_3 Completed
> 877 2005-01-19 00:00:00.000 Subject_4 Not Started
> Thanks a lot for your help.

With the example you have given, you would get away with:

select ano,max(date),a_subject, MIN(status)
from MY_TAB
where table_name='xyz' and ano=877
group by a_subject,ano
order by a_subject

But this does not work well, if you have Status values like "Anticipating"
which represents intermediate stages.

If you are looking for the status on the selected date, then this might be
better:

SELECT a.ano, a.maxdate, a.a_subject, b.status
FROM (select ano, maxdate, max(date), a_subject
from MY_TAB
where table_name='xyz' and ano=877
group by a_subject, ano) AS b
JOIN MY_TAB b ON a.ano = b.ano
AND a.maxdate = b.date
AND a.a_subject = b.a_subject

This assumes that ano/a_subject/date constitutes some form of key in
the table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things when they can.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.

As you can see, things happen "all at once" in SQL, not from left to
right as they would in a sequential file/proceudral language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.

Group by and Order By Problem

I have the following statement that is working just fine:

SQL = "select* from products GROUP BY name ORDER BY price"

I have many products with the same name but with different prices and I want to display unique records along with other information but I want to display the cheapest product first. It is displaying the first record in the table with that name but not the cheapest distinct record in the list of all distinct product names.

Not sure of the above makes sense so this is the kind of data in the table.

Name= Test1, Price= 25

Name= Test2, Price= 20

Name= Test1, Price= 15

Name= Test3, Price=30

When listing the products it will show the following order:

Test2, 20

Test1, 25

Test3, 30

However I want it to display:

Test1, 15

Test2, 25

Test3, 30

Thanks for any help.

Daniel

What version of SS are you working with?

In case of having same product with same price multiple times, then we need a tie breaker in order to make the solution deterministic.

select *

from dbo.product as a

where not exists (

select *

from dbo.product as b

where b.[name] = a.[name] and b.price < a.price

)

select *

from dbo.product as a

where price = (

select min(b.price)

from product as b

where b.[name] = a.[name]

)

select *

from dbo.product as a

where price = (

select top 1 b.price

from product as b

where b.[name] = a.[name]

order by b.price

)

-- 2005

with cte

as

(

select *, row_number() over(partition by [name] order by price) rn

from dbo.product

)

select *

from cte

where rn = 1

go

AMB

|||Just a kind reminder, the 2000 and 2005 solution may not return the same results.
Such as
Name= Test1, Price= 25
Name= Test2, Price= 20
Name= Test1, Price= 15
Name= Test3, Price=30
Name= Test1, Price=15

2000 Version return:

Test1,15
Test2,20
Test3,30
Test1,15

2005 version return:
Test1,15
Test2,20
Test3,30

To make two solutions equal, it needs add DISTINCT to outer select.

Such as:

select DISTINCT *

from dbo.product as a

where not exists (

select *

from dbo.product as b

where b.[name] = a.[name] and b.price < a.price

)

|||

You are right. I should have used dense_rank instead row_number or "distinct" as you mentioned. I would prefer to include a second column, like transaction_id, as the tie breaker.

AMB

GROUP BY and ORDER BY

hi Guys!
I am having a table
customer {
Name varchar(255),
step int,
details varchar(255)
}
I am trying to get the list of customer name with GROUP BY, and order
it using ORDER BY
SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
any suggestions?Hi
You'll have to include a step column within a SELECT stratement (at least)
and then ORDER BY this column
<sharma.vasudev@.gmail.com> wrote in message
news:1133327048.081452.157790@.z14g2000cwz.googlegroups.com...
> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||Hi Vasudev,
There is a problem with Table design, looking at the query you are executing
.
Try the following query. Please note that I am using Step in select list
(for same error u got).
SELECT Distinct Name,Step FROM customer ORDER BY step
Vishal Khajuria
9886170165
IBM Bangalore
"sharma.vasudev@.gmail.com" wrote:

> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||ok, i did that with following query
SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?|||hi Vishal,
i am really new with SQL stuff, kinda newbie, I am not sure what you
are pin-pointing to when you said 'There is a problem with Table
design' i would really appreciate if you could explain me a bit?
/dev|||To make things easy for everyone, can you just post some sample rows and
expected result?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"hack_tick" <sharma.vasudev@.gmail.com> wrote in message
news:1133336153.369184.296940@.o13g2000cwo.googlegroups.com...
> ok, i did that with following query
> SELECT Name, step from customer group by Name order by step
> it came out with following error,
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'customer.step is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> so i also tried GROUP BY
> SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
> but as expected it returned the result which was of no use to me :(
> any suggestions?
>|||Hi Vasudev,
The point to be noticed here is that the table does not conform to rules of
normalization. You should have one more table for Name of customers.
As per your question you were trying to get list of cusotmer names by using
Group By clause --
I did not see any reason why do you need group by clause.
What I could guess is that you want distinct names of customer, that is why
I suggested query with distinct and not group by. But here also you need to
keep step in Select clause.
Please let me know if you have any more questions.
--
Vishal Khajuria
9886170165
IBM Bangalore
"hack_tick" wrote:

> hi Vishal,
> i am really new with SQL stuff, kinda newbie, I am not sure what you
> are pin-pointing to when you said 'There is a problem with Table
> design' i would really appreciate if you could explain me a bit?
> /dev
>|||hi Vishal
The Table used was just for explanation, I am having a much bigger
table with 100's of columns and many complex relationship, also the
format of table is fixed and not suppose to change :(
I shall post a complete test case with insert in a while :)|||hi guys! maybe you all can have a look at the following query!
I need to have the name of the customer displayed, but they have to be
order by column 'step'
create table customer (
name varchar(255),
step int,
details varchar(255)
)
insert into customer values('cust-1', 1, 'details-1')
insert into customer values('cust-2', 2, 'details-2')
insert into customer values('cust-2', 3, 'details-3')
insert into customer values('cust-2', 4, 'details-4')
insert into customer values('cust-3', 5, 'details-5')
insert into customer values('cust-3', 6, 'details-6')
insert into customer values('cust-3', 3, 'details-7')
insert into customer values('cust-1', 4, 'details-8')
insert into customer values('cust-4', 5, 'details-9')
insert into customer values('cust-4', 1, 'details-10')
insert into customer values('cust-5', 1, 'details-11')
insert into customer values('cust-6', 3, 'details-11')
select name from customer group by name
i have tried following queries with following error
1) SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
2) SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?
PS: Sorry for any redundant data from my earlier post, just wanted to
have a common place for your all to look at :)|||what I want is to have the List of UNIQUE Customer name, but they have
to be sorted using field 'step'