Easy select distinct question.....

Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp
Hi
"C" wrote:

> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John
|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Easy select distinct question.....

Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi
"C" wrote:

> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
>
>
>
> But this won't give you any particular row. It will be a "frankenstein" r
ow
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-

Easy select distinct question.....

Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dpHi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> > Hi dp,
> > how should that look like, onyl distincing to one column ? If you want
> > one row per Distinct value, you will have to aggregate the other
> > columns with aggregate expressions like:
> > SELECT A,MON(B),MIN(C)
> > FROM SoneTable
> > Group BY A --This is the Distinct Column
> But this won't give you any particular row. It will be a "frankenstein" row
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-:)

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>

Dynamic SQL Queries!

An ASP application retrieves the DISTINCT records from all the columns
of a SQL Server DB table & populates them in drop-down lists. The no.
of drop-down lists on the web page depends upon the no. of columns in
the DB table. For e.g. if the DB table has, say, 5 columns, the web
page will show 5 drop-down lists.
Assume that the DB table stores information pertaining to books like
book name, category to which the book belongs to like sports, science,
music etc., author, publisher, publishing date etc.
Now suppose that a user selects an author named Author1 from the
drop-down list. When he does so, the page should get submitted & all
books that Author1 has penned should be displayed to the user. For e.g.
if Author1 has written 10 books, the user should be shown 10 records.
Now after Author1 has been selected & the appropriate records displayed
to the user, suppose the user selects an option from another drop-down
list like for e.g. the publisher drop-down list. An author can get his
books published by different publishers. When the user selects, say,
Publisher1, from the drop-down list, the user should now be displayed
all the records that Author1 has written BUT which have been published
by Publisher1 only. Now Author1 has written 10 books. Out of these 10
books, Publisher1 has published 4 books. So under such circumstances, 4
records should be retrieved & displayed to the user. The SQL query
would be
SELECT * FROM tblBooks WHERE Author='Author1' AND
Publisher='Publisher1'
The problem I am having is in adding the second WHERE clause i.e. 'AND
Publisher='Publisher1' in the SELECT query. Please note that all the
drop-down lists EXCEPT for the author drop-down list should change
again & contain only those records as options which are common to both
Author1 & Publisher1.
Arpan> The problem I am having is in adding the second WHERE clause i.e. 'AND
> Publisher='Publisher1' in the SELECT query.
So what is the problem? That query is syntactically valid so it isn't
obvious what your question is.
I would have expected separate tables for publisher and books joined by
a third table for the many-to-many relationship. Is it the join that
you have a problem with? For example:
SELECT ...
FROM tblBooks AS B
JOIN tblBookPublishers AS J
ON B.isbn = J.isbn
JOIN tblPublishers AS P
ON J.publisher_id = P.publisher_id
WHERE B.Author='Author1'
AND P.Publisher='Publisher1' ;
And by the way, books can have more than one author too, so author
probably shouldn't appear in the books table and you need at least two
more tables there.
If you need more help, please read this first:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I do understand that your are right in saying that the records should
have been in seperate tables but the fact is the database that my
clients have wasn't created by a database expert; so they have all the
records in one table only & don't wish to seperate the records in
different tables because of time constraint.
Had related records been in different tables, there wouldn't have been
any problem but since that isn't the case, my problems have increased.
Any further suggestions?|||> Any further suggestions?
What is your question?
Please also include a CREATE TABLE statement if your question is about
a query.
David Portas
SQL Server MVP
--