grouping data

some one plz help me.
I had a table with these columns.
Table(Id int,Name varchar,Value Varchar).
I have to group them by ID and each Name becomes column name of the new table

ex:-
Id Name Value
-------
1 x a1
2 x a2
3 x a3
1 y b1
2 y b2
3 y b3
1 z c1
2 z c2
3 z c3

I need it in this way

x y z
----
a1 b1 c1
a2 b2 c2
a3 b3 c3


(no of columns in the new table can't be pre determined)

and which one would be better option to do this
in VB.Net code or in a Storedprocedure?You can do this in a stored procedure using CASE statements. You can find a fine explanation of the method by searching for "Cross-Tab Reports" in Books online.|||Normally I'd recommend that you do this on the client, but it can (usually) be done on the server. Just to prove that, I wrote a snippet of code that seems to work, although it doesn't deal well with ill-behaved data. FWIW, my code is:CREATE TABLE tPivot (
id INT NOT NULL
, name VARCHAR(20) NOT NULL
, value VARCHAR(20) NOT NULL
)

INSERT tPivot (id, name, value) VALUES (1, 'x', 'A1')
INSERT tPivot (id, name, value) VALUES (2, 'x', 'A2')
INSERT tPivot (id, name, value) VALUES (3, 'x', 'A3')

INSERT tPivot (id, name, value) VALUES (1, 'y', 'B1')
INSERT tPivot (id, name, value) VALUES (2, 'y', 'B2')
INSERT tPivot (id, name, value) VALUES (3, 'y', 'B3')

INSERT tPivot (id, name, value) VALUES (1, 'z', 'C1')
INSERT tPivot (id, name, value) VALUES (2, 'z', 'C2')
INSERT tPivot (id, name, value) VALUES (3, 'z', 'C3')

DECLARE @.cmd NVARCHAR(4000)
DECLARE @.cName SYSNAME

SELECT @.cmd = 'SELECT DISTINCT id'

DECLARE zName CURSOR FOR SELECT DISTINCT
name
FROM tPivot

OPEN zName
FETCH zName INTO @.cName

WHILE 0 = @.@.fetch_status
BEGIN
SELECT @.cmd = @.cmd +
', (SELECT Min(value) FROM tPivot AS b'
+ ' WHERE b.id = a.id'
+ ' AND b.name = ''' + @.cName
+ ''') AS [' + @.cName + ']'

FETCH zName INTO @.cName
END

CLOSE zName
DEALLOCATE zName

SELECT @.cmd = @.cmd + ' FROM tPivot AS a'

SELECT @.cmd
EXECUTE (@.cmd)Note that even though this CAN be done on the server, it would be better handled on the client side in most cases.

-PatP|||Hey Pat, you just love cursors, don't you?

declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')

select [x], [y], [z] from (
select distinct ID from @.tbl) t1
left outer join (
select ID, [x] = Value from @.tbl where Name = 'x') t2
on t1.ID = t2.ID
left outer join (
select ID, [y] = Value from @.tbl where Name = 'y') t3
on t1.ID = t3.ID
left outer join (
select ID, [z] = Value from @.tbl where Name = 'z') t4
on t1.ID = t4.ID|||Originally posted by rdjabarov
Hey Pat, you just love cursors, don't you? Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.

-PatP|||Who loves left-joins and subqueries?

declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')

select max(case when Name = 'x' then Value end) as x,
max(case when Name = 'y' then Value end) as y,
max(case when Name = 'z' then Value end) as z
from @.tbl
group by ID|||Originally posted by theguru
(no of columns in the new table can't be pre determined)
Was this part of the spec optional ?

-PatP|||Originally posted by Pat Phelan
Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.

-PatP

I saw the spec and stayed away...

Blenderized data anyone?

Salt or No Salt?|||Originally posted by Brett Kaiser
Salt or No Salt? Stayed away ? Don't you mean you're wasting away... oops, wait a sec, you're headed there anyway!

-PatP|||Not until 5:00 Pat...

Hey, look at that...it's 5:00!

See ya....|||"(no of columns in the new table can't be pre determined)"

Crap. Always read the fine print...

theguru, I've seen posts for fully dynamic SQL code that will do on-demand cross tabs, though I haven't tested them. rdjabarov, didn't you have one? It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released.|||Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656? Or even more realistic, like 2972? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?|||Originally posted by blindman
It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released. At least I think that is what my code sample does. That is exactly why I had to resort to a cursor to build dynamic code, even though both of those go against my better judgement!

-PatP|||Whoa! Please go get your cig!

People ask this question because
A) They are trying to format the data for reporting
and
2) They were weaned on MS Access and its wonderfully convenient and fully dynamic cross-tab functionality.

Problem is, theguru, that when you don't know the number of columns or the names of the columns, most reporting applications (such as Crystal or even MS Access' reports) will choke on the output.

Perhaps your best bet would be to load the data into a pivot table in flat-file format, and then slice-and-dice however you want. How pretty does the output need to be?

Pat Phelan, I like your idea, though I haven't tried it out. I'd call it semi-dynamic, since you are working with a defined table format. A fully-dynamic method applicable to any dataset is the Holy Grail of cross-tab reporting.|||Originally posted by rdjabarov
Isn't it indicative of both poor app and poor database design? I'm not prepared to argue that point. We'd both be "preaching to the choir" on this one!

Originally posted by rdjabarov
But I'd just stress the first one, - who in their sober mind would design an application that would produce such output? What makes you think that the designer was in their sober mind ? ;)

-PatP|||Hey, at least my problems can be answered with a cig! You guys are sitting on a poor design and kicking this horse with "non-determined" number of legs wondering if it's gonna ever run again (tsk, it's been dead for a while...)|||See what happens when we both start typing really fast?|||Originally posted by rdjabarov
See what happens when we both start typing really fast? Yeah, but it's fun to watch!

-PatP|||Thank you all for u r replies.
Well "the number of columns cannot be pre..." doesn't mean that no of columns may exceed a 2 digit number atmost 20 columns.
ok I will try this out in stored procedure with my actual data.

thanks once again..keep sending u r suggestions...

Originally posted by rdjabarov

Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656? Or even more realistic, like 2972? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?|||sorry, I think I might be missing some thing here.
This will work when I am sure of occurance x,y,z names exactly in the table
but it is not case here x,y,z may be reffered with some other names like A,B,C or O,P,Q which can't be presumed.

Originally posted by blindman
Who loves left-joins and subqueries?

declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')

select max(case when Name = 'x' then Value end) as x,
max(case when Name = 'y' then Value end) as y,
max(case when Name = 'z' then Value end) as z
from @.tbl
group by ID|||Great...I go out and slam some 'ritas...get called back in...yeah there;s a concept..to fix a prod problem and everyone is going nuts...

The point is mute...

It's still bender data...

No?|||Just curious at this point, but have you tried my code with your data?

-PatP|||yup,
thank u.but I am waiting for some more options.
any way I will use this one for the time being.
thank u.


Originally posted by Pat Phelan
Just curious at this point, but have you tried my code with your data?

-PatP|||Just a note...

1. I'm exhausted...

2. What's the point of your result set? It makes no sense.

Is this homework?|||This is what i need to show to my client.
I can't change the DB design at this point.I have to do this at any cost, performance is an exception for this.

Originally posted by Brett Kaiser
Just a note...

1. I'm exhausted...

2. What's the point of your result set? It makes no sense.

Is this homework?

grouping and concatenating

i have a the following table:

CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO

for each wordID i have many paraID and for each wordID,paraID i have many wordPos

i will use the following convention:
paraID=p
wordID=w
wordPos=wp

i want to concatenate the columns to get the following format:

row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...

where NB1 is the number of wp having w1 and p1

Note: the length of a row may exceed 8000 charsOriginally posted by samham
Note: the length of a row may exceed 8000 chars If any column exceeds 8000 characters (which is what I think you are trying to say), then you have no choice... You must build those columns on the client.

-PatP|||the total rows of the table is 15 million

i am trying to copy the table content to a text file in the format i described

I put the note about the 8000 chars to say that 1 row cannot be contained in a varchar(8000) variable in case concatenating using a varchar(8000) is a solution

i am using c# as my programming language so my last option is to do this by c# code by selecting wordID and then for each rowID select the paraID and then for each wordID,paraID select the wordPos

but i was wondering if this can be done by sql and then send the result directly to a textfile|||Sorry, SQL Server can't do what you want. According to SQL Maximum Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), the maximum row size for SQL Server is 8060 bytes. The maximum single string is 8000 bytes.

In your case, your last resort is the only one that might work.

-PatP|||Ok Pat thank you
i will just go for the c# solutionsql

group by and sum

Hi,
I have a table like this;
WAREHOUSE_STOCKS
--
WAREHOUSE_NAME (varchar)
ITEM_NAME (varchar)
LOT_NAME (varchar)
QUANTITY (int)
and these are records,
| WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
Wr-1, It-1, Lt-1, 10
Wr-1, It-1, Lt-2, 20
Wr-1, It-2, Lt-1, 10
Wr-2, It-1, Lt-1, 5
Wr-2, It-1, Lt-2, 20
Wr-2, It-1, Lt-3, 10
I want to write SQL and show sum(quantity) "group by" warehouse and item,
like this;
| WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
Wr-1, It-1, 30
Wr-1, It-2, 10
Wr-2, It-1, 35
How can I do?
thanksSeems to be a simple GROUP BY with aggregate function:
SELECT warehouse_name, item_name, SUM(quantity) AS quantity
FROM warehouse_stock
GROUP BY warehouse_stock, item_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yunus Efe" <Yunus Efe@.discussions.microsoft.com> wrote in message
news:5D27FE26-FACE-476C-B639-B170A894383E@.microsoft.com...
> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>|||Try,
select WAREHOUSE_NAME, ITEM_NAME, sum(QUANTITY) as sum_QUANTITY
from dbo.WAREHOUSE_STOCKS
group by WAREHOUSE_NAME, ITEM_NAME
See "select statement" in BOL.
AMB
"Yunus Efe" wrote:

> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>|||select WAREHOUSE_NAME , ITEM_NAME, sum(QUANTITY )
from WAREHOUSE_STOCKS
group by WAREHOUSE_NAME , ITEM_NAME
"Yunus Efe" <Yunus Efe@.discussions.microsoft.com> wrote in message
news:5D27FE26-FACE-476C-B639-B170A894383E@.microsoft.com...
> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>

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'

group by and min()

I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
int, others varchar. Assume the following data.
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
I need to group by c3 and return the c2 that corresponds with the min()
of c1. I know I can write a simple function like below but would like
to know if there's a better way... ie no function.
select min(c1), dbo.getr2(c1, c3), c3
from table
group by c3
results
1 d22 d111
3 d33 d333
Thanks in advance.
jghwhat about select c3, min(c1) from table group by c3
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<justingharvey@.yahoo.com> wrote in message
news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...
>I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I need the c2 that corresponds with the min(c1).
tks
Hilary Cotter wrote:
> what about select c3, min(c1) from table group by c3
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <justingharvey@.yahoo.com> wrote in message
> news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...
> >I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> > int, others varchar. Assume the following data.
> >
> > c1 c2 c3
> > --
> > 2 d11 d111
> > 1 d22 d111
> > 3 d33 d333
> >
> > I need to group by c3 and return the c2 that corresponds with the min()
> > of c1. I know I can write a simple function like below but would like
> > to know if there's a better way... ie no function.
> >
> > select min(c1), dbo.getr2(c1, c3), c3
> > from table
> > group by c3
> >
> > results
> > 1 d22 d111
> > 3 d33 d333
> >
> >
> >
> > Thanks in advance.
> >
> > jgh
> >|||Hi,
Is what you what the C2 corresponding to the minimum value of C1 for each
distinct value of C3?
If so the following may be along the right lines:-
DROP TABLE Test
CREATE TABLE Test
(
C1 int,
C2 varchar(5),
C3 varchar(5)
)
INSERT INTO Test
(C1, C2, C3)
VALUES
(2, 'd11', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(1, 'd22', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(3, 'd33', 'd333')
SELECT C2
FROM TEST INNER JOIN
( SELECT MIN(C1) AS C1, C3
FROM TEST
GROUP BY C3
) AS MinC1ForEachC3
ON Test.C1 = MinC1ForEachC3.C1
AND Test.C3 = MinC1ForEachC3.C3
It might be what you need ... if not let us know :)
Craig
"justingharvey@.yahoo.com" wrote:
> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I'd do it like this:
select c1, c2, c3 from table
inner join
(
select min(c1) as Minc1, c3
from table
group by c3
) a
on table.c1 = a.Minc1
and table.c3 = a.c3
The function solution seems incorrect (or perhaps I didn't follow what your
function does), as the derivation needs to be applied after the grouping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||How about ...
select c2 from Table1 where C1 IN
(
select min(c1) from Table1 group by c3
)
"justingharvey@.yahoo.com" wrote:
> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||It wouldn't work. If we add another row to the table (at the bottom):
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
2 d44 d444
The subquery returns 1,2,3 so the outer query now returns d11,d22,d33,d44,
but d11 shouldn't be returned.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

group by + rowcount

I have a simple query with group by.
and I want to get the topX rows from each group.
how to do that ?
for example
declare @.myTable table ( id int identity(1,1),
comment int,
someDate datetime
)
select comment, someDate
from @.myTale
group by commentNoam
declare @.myTable table ( id int identity(1,1),
comment int,
someDate datetime
)
insert into @.myTable (comment,someDate)values (1,'20050101')
insert into @.myTable (comment,someDate)values (1,'20050102')
insert into @.myTable (comment,someDate)values (1,'20050103')
insert into @.myTable (comment,someDate)values (2,'20040101')
insert into @.myTable (comment,someDate)values (2,'20040102')
insert into @.myTable (comment,someDate)values (2,'20040103')
select comment, someDate from @.myTable t
where somedate in(select top 2 somedate from @.myTable a where
a.comment=t.comment
order by somedate desc)
"Noam" <anonymous@.discussions.microsoft.com> wrote in message
news:182c01c50f7a$196e4150$a601280a@.phx.gbl...
> I have a simple query with group by.
> and I want to get the topX rows from each group.
> how to do that ?
> for example
> declare @.myTable table ( id int identity(1,1),
> comment int,
> someDate datetime
> )
> select comment, someDate
> from @.myTale
> group by comment
>|||Then do not use group by.
Example:
use northwind
go
declare @.i int
set @.i = 3
select
c.country,
oh.orderid
from
orders as oh
inner join
customers as c
on oh.customerid = c.customerid
where
(select count(*) from orders as a inner join customers as b on a.customerid
= b.customerid where b.country = c.country and a.orderid >= oh.orderid) <= @.
i
order by
c.country,
oh.orderid
go
AMB
"Noam" wrote:

> I have a simple query with group by.
> and I want to get the topX rows from each group.
> how to do that ?
> for example
> declare @.myTable table ( id int identity(1,1),
> comment int,
> someDate datetime
> )
> select comment, someDate
> from @.myTale
> group by comment
>|||Hi Noam,
Hope By now you have got the solution.
but I am afraid your query for the original groupby does not appear to be
syntatically correct. you should have samecolumns in the group by as well as
Select List in your case.
Jai
"Noam" wrote:

> I have a simple query with group by.
> and I want to get the topX rows from each group.
> how to do that ?
> for example
> declare @.myTable table ( id int identity(1,1),
> comment int,
> someDate datetime
> )
> select comment, someDate
> from @.myTale
> group by comment
>

GRMPH!

CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
@.description NVARCHAR(70),
@.jobTitleKey INT
) AS
UPDATE msl_JobTitle
SET Description = @.description
WHERE JobTitleKey = @.jobTitleKey
GO
Then I have some VB.NET code that runs it:
Private Sub fixJobTitles()
Dim cnstr As String =
ConfigurationSettings.AppSettings("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
Dim dt As New DataTable()
cmGet.CommandType = CommandType.Text
cmSave.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmGet)
Dim tx As New Transform()
With cmSave
.Parameters.Add("@.description", "")
.Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
End With
da.UpdateCommand = cmSave
Try
da.Fill(dt)
Catch exc As Exception
Console.WriteLine(exc.Message)
End Try
For Each dr As DataRow In dt.Rows
dr("Description") =
tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
Next
Try
da.Update(dt)
Catch exc As Exception
'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
Console.WriteLine(exc.Message)
End Try
End Sub
AM I RETARDED OR SOMETHING?!?!
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldObviously!
How do you expect the dataadapter object to know which columns in your
datatable object to use to satisfy the parameters of the updatecommand
object.
You need to do some table/column mapping to bind the correct parameter to
the correct column.
Check out the overloads of the sqlparameter object constructor.
In addition, you datatable object will not have a schema until you have
'filled' it, so you won't be able to map the columns until after the fill.
You could, however, explicitly define the schema so that the datatable is
ready for column mapping at an earlier stage.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uswh4P9rFHA.332@.tk2msftngp13.phx.gbl...
> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||per BOL
Use caution when using this overload of the Add method to specify integer
parameter values. Because this overload takes a value of type Object, you
must convert the integral value to an Object type when the value is zero, as
the following C# example demonstrates.
parameters.Add("@.pname", Convert.ToInt32(0));
"Mike Labosh" wrote:

> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||Mike,
I think you should set the sourceColumn for that parameter.
Using Parameters with a DataAdapter
http://msdn.microsoft.com/library/d...ataadapters.asp
AMB
"Mike Labosh" wrote:

> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||You should not half to do any of that. What you have posted as code i used
daily. With out mapping the source column.
There is 1 thing that I have noticed with out mapping the source is that
your parameters order in your stored procedure must match your parameters
order in your code.
should be as so
dim cn as new sqlclient.sqlconnection("Provider string")
dim cm as new sqlclient.sqlcommand("sqltext or stored proc",cn)
cm.commandtype = commandtype.storedprocedure
cm.parameters.add("@.Parmname","parmvalue")<-- in order here
cn.open
cm.executenonquery
cn.close
that will work every time. I have never had a problem with not mapping the
source.
post your database class.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C14455AC-40C6-417A-A332-7D16FEC98D91@.microsoft.com...
> Mike,
> I think you should set the sourceColumn for that parameter.
> Using Parameters with a DataAdapter
> http://msdn.microsoft.com/library/d...ataadapters.asp
>
> AMB
> "Mike Labosh" wrote:
>|||Chris,
I do not know if you got the problem here. The code you posted has nothing
to do with the OP problem. He is using this command for the
SqlDataAdapter.UpdateCommand.

> cm.parameters.add("@.Parmname","parmvalue")<-- in order here
That is not necessary because ado.net call the sp using named parameters by
default, and not by position as ado used to do it (that is the reason why ad
o
command object has a property NamedParameters). What should match is the nam
e
of the command parameter with the name of the sp parameter. See "Using
Parameters with a SqlCommand" in the following link.
Using Stored Procedures with a Command
http://msdn.microsoft.com/library/d...withcommand.asp
AMB
"Chris" wrote:

> You should not half to do any of that. What you have posted as code i used
> daily. With out mapping the source column.
> There is 1 thing that I have noticed with out mapping the source is that
> your parameters order in your stored procedure must match your parameters
> order in your code.
>
> should be as so
> dim cn as new sqlclient.sqlconnection("Provider string")
> dim cm as new sqlclient.sqlcommand("sqltext or stored proc",cn)
> cm.commandtype = commandtype.storedprocedure
> cm.parameters.add("@.Parmname","parmvalue")<-- in order here
> cn.open
> cm.executenonquery
> cn.close
> that will work every time. I have never had a problem with not mapping the
> source.
> post your database class.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:C14455AC-40C6-417A-A332-7D16FEC98D91@.microsoft.com...
>
>|||you are correct.. Sorry about that miss post. I did not read it correct.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1F7A7D17-B6DE-4203-834A-19D7E3226F62@.microsoft.com...
> Chris,
> I do not know if you got the problem here. The code you posted has nothing
> to do with the OP problem. He is using this command for the
> SqlDataAdapter.UpdateCommand.
>
> That is not necessary because ado.net call the sp using named parameters
> by
> default, and not by position as ado used to do it (that is the reason why
> ado
> command object has a property NamedParameters). What should match is the
> name
> of the command parameter with the name of the sp parameter. See "Using
> Parameters with a SqlCommand" in the following link.
> Using Stored Procedures with a Command
> http://msdn.microsoft.com/library/d...withcommand.asp
>
> AMB
> "Chris" wrote:
>