DISTINCT on one column only

I have a table I'm running a query on:
SELECT DISTINCT guid, Department FROM table
I only want rows with unique guid's to be returned (there are a couple rows
with identical guids, and I can't fix the real problem of having multiple
guids)
This returns rows with distinct guids and departments obviously. I tried to
modify the query to:
SELECT DISTINCT(guid), Department FROM table
Trying to get it to run the distinct on just the guid column. Still didn't
do it.
What do I need to do to get just the unique guids?
PS The query:
SELECT DISTINCT guid, FROM table
works perfectly.Based on your narrative, you seem to be struggling with a poorly chosen
identifier namely guid. In any case, DISTINCT return distinct rows from a
table, to extract distinct values from a column you will have to use an
aggregate function with a GROUP BY clause like:
SELECT MAX( guid ), department
FROM tbl
GROUP BY department ;
Anith|||SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
David Portas
SQL Server MVP
--|||The unit of work in a SELECT statement is a entire **row**, not a
**column**. The SELECT DISTINCT is for a whole row. You still think
this is "left to right, one field at a time" file system. No wonder
you would have such a poor choice of keys -- you are mimicing a record
number in a file system. You need to stop programming and get a book
on RDBMS basics.
Actually, you need to get rid of that GUID column and get a valid
relational key.
SELECT silly_guid
FROM Foobar
GROUP BY silly_guid
HAVING COUNT(*) = 1;|||- Steve - wrote:
> I have a table I'm running a query on:
> SELECT DISTINCT guid, Department FROM table
> I only want rows with unique guid's to be returned (there are a
> couple rows with identical guids, and I can't fix the real problem of
> having multiple guids)
> This returns rows with distinct guids and departments obviously. I
> tried to modify the query to:
> SELECT DISTINCT(guid), Department FROM table
> Trying to get it to run the distinct on just the guid column. Still
> didn't do it.
> What do I need to do to get just the unique guids?
> PS The query:
> SELECT DISTINCT guid, FROM table
> works perfectly.
You need to figure out which duplicate qualifies as the row you want
returned and then implement the technique that David and Anith describe.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is really close to what I want.
The only problem is that the two rows with the same guid but different
departments, isn't showing at all. I'd like one line of it to show up. (I
don't care which one)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121876475.045441.292920@.g47g2000cwa.googlegroups.com...
> SELECT guid, MIN(department)
> FROM tbl
> GROUP BY guid
> --
> David Portas
> SQL Server MVP
> --
>|||> Actually, you need to get rid of that GUID column and get a valid
> relational key.
I only get to use the data. I have no saying whatsoever in how the data is
managed. It's completley out of my control.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121877566.139416.161400@.g47g2000cwa.googlegroups.com...
> The unit of work in a SELECT statement is a entire **row**, not a
> **column**. The SELECT DISTINCT is for a whole row. You still think
> this is "left to right, one field at a time" file system. No wonder
> you would have such a poor choice of keys -- you are mimicing a record
> number in a file system. You need to stop programming and get a book
> on RDBMS basics.
>
> Actually, you need to get rid of that GUID column and get a valid
> relational key.
> SELECT silly_guid
> FROM Foobar
> GROUP BY silly_guid
> HAVING COUNT(*) = 1;
>|||What you've described isn't what I'd expect. Try the following, which
works for me:
CREATE TABLE tbl (guid UNIQUEIDENTIFIER NOT NULL, department
VARCHAR(10) NOT NULL /* PRIMARY KEY not specified */)
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','A'
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','B'
INSERT INTO tbl SELECT 'C1864626-28CE-4BE3-8171-F4E989DDF114','C'
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
Result:
guid
-- --
9EF7940E-B5A9-4E81-8959-244A7CF31E5F A
C1864626-28CE-4BE3-8171-F4E989DDF114 C
(2 row(s) affected)
Did you do something different? Post some code to reproduce it (like
I've done) if you need more help.
Note that you could also show just the duplicated rows:
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||>> I only get to use the data. I have no saying whatsoever in how the data
is managed. It's completley out of my control. <<
Sorry about that.
I do not drive the train
I cannot ring the bell
but let the damn thing jump the track
and see who catches Hell.

Displaying values from Informix Stored Procedure

I am not able to see the values returned from my stored procedure in the
report preview, but I am able to see them in the Data window. I made sure
that I went in and mapped the fields for each dataset that I am using in the
report, but this still does not work. Any ideas?
TIA,
JeremyAnyone?
"Chancetribe" wrote:
> I am not able to see the values returned from my stored procedure in the
> report preview, but I am able to see them in the Data window. I made sure
> that I went in and mapped the fields for each dataset that I am using in the
> report, but this still does not work. Any ideas?
> TIA,
> Jeremysql

Displaying Header on each page

I am new to Report Services. CurrentlyI have mulitple pages of
information returned from my query and I would like to know if it is
possible to display the header information at the top of each page as
it is called by the user. At the moment it will only be present on the
first page.
Any help would be much appreciated.
IvanSimplest way is to incorporate your header info into the data region
(table, matrix, etc.) of your report.
Good luck and good reporting!!|||If it is group header that you want repeated you can click the end of a row
that has the group and then in the properties window you will see the option
to repeat on new page and you can set it to true.
"Ivan" wrote:
> I am new to Report Services. CurrentlyI have mulitple pages of
> information returned from my query and I would like to know if it is
> possible to display the header information at the top of each page as
> it is called by the user. At the moment it will only be present on the
> first page.
> Any help would be much appreciated.
> Ivan
>|||Ivan,
There is a group property called "RepeatOnNewPage" that can be set on
the group's header or footer lines.
Kent
Ivan wrote:
> I am new to Report Services. CurrentlyI have mulitple pages of
> information returned from my query and I would like to know if it is
> possible to display the header information at the top of each page as
> it is called by the user. At the moment it will only be present on the
> first page.
> Any help would be much appreciated.
> Ivan

displaying found duplicate - both records

I would like to look at both records returned as duplicates. I am using the
following script to find the duplicates but can it only returns one record
not both. I would like to see the entire record so that I can make a
determination on more than just the criteria that I selected as the basis of
the record being a duplicate.
SELECT Patient.Last_Name,
Patient.First_Name,
Patient.Birthdate
COUNT(*) AS Dupes
FROM Patient
WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
Payor_Key = 36)
GROUP BY Last_Name, First_Name, Birthdate
HAVING (COUNT(*) > 1)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1In order to do that, you may need to put your current query as "Result
Table", something like:
SELECT P1.Last_Name,
P1.First_Name,
P1.Birthdate,
Dupes
FROM Patient P1,
(SELECT P2.Last_Name P2LastName,
P2.First_Name P2FirstName,
P2.Birthdate P2Birthday
COUNT(*) AS Dupes
FROM Patient P2
WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FROM
Patient_Elg WHERE
P2.Payor_Key = 36)
GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
HAVING (COUNT(*) > 1)) DupCount
where P1.Last_Name = P2LastName
and P1.First_Name = P2FirstName
and P1.Birthdate = P2Birthday
This code has not been tested yet but just an idear.
Perayu
"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:5685c45d8a0c6@.uwe...
>I would like to look at both records returned as duplicates. I am using the
> following script to find the duplicates but can it only returns one record
> not both. I would like to see the entire record so that I can make a
> determination on more than just the criteria that I selected as the basis
> of
> the record being a duplicate.
> SELECT Patient.Last_Name,
> Patient.First_Name,
> Patient.Birthdate
> COUNT(*) AS Dupes
> FROM Patient
> WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
> Payor_Key = 36)
> GROUP BY Last_Name, First_Name, Birthdate
> HAVING (COUNT(*) > 1)
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||This did work for me after I made some tweaks. Thanks allot.
Perayu wrote:
>In order to do that, you may need to put your current query as "Result
>Table", something like:
>SELECT P1.Last_Name,
> P1.First_Name,
> P1.Birthdate,
> Dupes
>FROM Patient P1,
> (SELECT P2.Last_Name P2LastName,
> P2.First_Name P2FirstName,
> P2.Birthdate P2Birthday
> COUNT(*) AS Dupes
> FROM Patient P2
> WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FRO
M
>Patient_Elg WHERE
> P2.Payor_Key = 36)
> GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
> HAVING (COUNT(*) > 1)) DupCount
>where P1.Last_Name = P2LastName
> and P1.First_Name = P2FirstName
> and P1.Birthdate = P2Birthday
>This code has not been tested yet but just an idear.
>Perayu
>
>[quoted text clipped - 13 lines]
Message posted via http://www.webservertalk.com

displaying error message - no rows returned

Hello,

I have a seach on UserID enetred in a textbox by user.

When the user types a userID which has no data in the database can I display an error msg instead of the chart and table that is displayed on entering a valid userID.

Thanks,

Kiran.

Hi,

For the table there is a property 'NoRows',in this propert you can write the message that is to be displayed when UserID is not matched.

Hope this helps

|||

yes,

Thant worked fine mahima,

appreciate the help..

displaying error message - no rows returned

Hello,

I have a seach on UserID enetred in a textbox by user.

When the user types a userID which has no data in the database can I display an error msg instead of the chart and table that is displayed on entering a valid userID.

Thanks,

Kiran.

Hi,

For the table there is a property 'NoRows',in this propert you can write the message that is to be displayed when UserID is not matched.

Hope this helps

|||

yes,

Thant worked fine mahima,

appreciate the help..

displaying "No Record found" Message

hi all,

i want to show a message when no rows is being returned from the data set,

i.e when there is no data to display the report should display

"No Record Found"

In the properties tab of table or matric or list ,you can find the property named "No Rows" , assign "No records found".

If you dont like the visibility the way it shows the "No records found".

1)Have a text box "No Records found"

2)Control the visibility of "No Records found" text box, either by the no of rows returned" i.e count(fields!field.value) or check for null,whcih ever is suitable to your scneario.

Thank you

|||thanks

Display return value from sqldatasource in textbox

Hi,

I wonder is there anyway I can put the returned value from SELECT statement from sqldatasource into a textbox.

I have a simple select statement in a sqldatasource:

SELECT X, Y, Z FROM tablexyz

So, I want the value from X, Y, Z to be displayed in 3 different textboxes. How do I do it? I am able to do it using datareader from code-behind, but I just want to know how to make use of the sqldatasource to make the whole things work.

Thanks

You could use either a gridview or a detailveiw to do that, and strip out all of the headers and footers.

I would have thought it would be easier to just use the datareader and some text boxes, I guess it depends on what you are trying to achieve.

HTH