distinct years

in my table I have a column Dates
10/03/2004 18:35:00
how can I get the list of Distinct years ?
2001
2002
2003
2004
thank youselect distinct year(Dates) from yourtable|||it works !

thank you

distinct values from a join

Is there any way i can get distinct values in one column from a join of 2
tables with the same columns? FOr example:
table1, column fname
frank
bob
bob
dave
frank
A distinct yields
frank
bob
dave
table2, column fname
bob
alan
dave
alan
I want to join these tables and get one column, fname, to have:
frank
bob
dave
alan
Thanks for any help.
Bernie YaegerNevermind- figured it out:
select distinct invnum from bnlsum union select distinct invnum from bnlsumr
Bernie
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eOLdvc%236FHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is there any way i can get distinct values in one column from a join of 2
> tables with the same columns? FOr example:
> table1, column fname
> frank
> bob
> bob
> dave
> frank
> A distinct yields
> frank
> bob
> dave
> table2, column fname
> bob
> alan
> dave
> alan
> I want to join these tables and get one column, fname, to have:
> frank
> bob
> dave
> alan
> Thanks for any help.
> Bernie Yaeger
>
>
>|||Hey Bernie,
Just as an FYI: a UNION query performs a DISTINCT inherently. Although
your performance plan may not change much, using DISTINCT and UNION in
the same query is redundant.
If your tables are large, you may see some benefit by running SELECT
Distinct colname... UNION ALL... That way, the DISTINCT selection is
performed in parallel before the rsults are joined.
Stu|||Hi,
You also try it as
SELECT distinct fname
FROM
(
Select fname from Table1
UNION
Select fname from Table2
)UNION_TABLE
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Bernie Yaeger" wrote:

> Is there any way i can get distinct values in one column from a join of 2
> tables with the same columns? FOr example:
> table1, column fname
> frank
> bob
> bob
> dave
> frank
> A distinct yields
> frank
> bob
> dave
> table2, column fname
> bob
> alan
> dave
> alan
> I want to join these tables and get one column, fname, to have:
> frank
> bob
> dave
> alan
> Thanks for any help.
> Bernie Yaeger
>
>
>

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

Distinct Sum for my column

Hi,

Bonjour,

I want distinct sum for one of my column.But iam not able to do that.

I tried DISTINCTSUM function given inMSDN, but it always return ZERO.

My function call in FOOTER section is called first, before my DETAILS section function call.

please help me for this.

thanks and regards

Hemant

You need to add an expression to your detail cells in the column, that expression should call a custom (code) function that records all unique values into an array:

=Code.AddUniqueNumber(myField)

then in your total just call another function that sums the array you built:

=Code.SumUniqueNumbers()

this is roughly what your code should look like:

Code Snippet

dim myArray() as Integer

public function AddUniqueNumber(Byval newNumber as integer) as integer

AddUniqueNumber = newNumber

dim i as integer

for i = lbound(myArray) to ubound(myArray)

'if this array element equals the number then it isn't unique

if myArray(i) = newNumber then exit sub

next

'increase the size of the array

redim preserve myArray(ubound(myArray) + 1)

'add the new unique number to it

myArray(ubound(myArray)) = newNumber

end function

public function SumUniqueNumbers() as Integer

dim sum as integer

dim i as integer

for i = lbound(myArray) to ubound(myArray)

sum = sum + myArray(i)

next i

SumUniqueNumbers = sum

end function

Note that this code is purely of the top of my head, my VBA is rusty, and it is UNTESTED and will have syntax errors. But it gives you an indication of how to do it. You will also need to initialise your array, probably by passing the rownum in as a parameter as well, and if the rownum = 1 then reinitialise the array.

|||

hi,

thanks for the reply.

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

-thanks and rgeards

Hemant

|||

HemantC wrote:

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

Then you are doing something wrong.... the code concept does work, i have used it in the past.

The columns in a report are evaluated left to right, top to bottom, so if your array was zero then maybe you have one of these things wrong:

- you have not inserted a call to add a value to the array in the detail rows (or you put the call in the wrong place)

- you are making the call correctly but not adding the new value to the array like you should

- you are reinitialising the array on every call, instead of on just the first row of the table

- you are not looping through the array correctly to sum it

- there is an error in the code and you are showing a zero instead of #ERROR

What i have found helpful in the past is to write the code in the macro editor of Excel, along with a test function that calls it, then once it is performing correctly i insert the code into the report.

|||

Hi,

Thanks again.

I deleted my table and again created new one.But my footer function is called first and then my details section.

For debugging i just put a messagebox, which shows that first footer function is called.

thanks and regards

Hemant.

|||Do you mean the page footer or do you mean the subtotal on a table?

If it is the former, then try referencing the code from a hidden text box in the page body, and then refer to the hidden text box from the footer using the "reportitems" collection|||

Its in footer.

I also tried in hidden textbox.

But we cant access textbox of details section in Footer section.It gives error.

thanks

Hemant

|||

this is my code

Public orderIDs As System.Collections.Hashtable
Public total As Double

Public function CalculateSum(ByVal orderID As Object, ByVal freight As Object) As Double

If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
CalculateSum = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
CalculateSum = total
End If
End Function

Public function SumUniqueNumbers() as Integer
System.Windows.Forms.MessageBox.Show("toto")
dim sum as integer
Dim myDE As System.Collections. DictionaryEntry

For Each myDE In orderIDs

sum =sum +myDE.Value
Next myDE
SumUniqueNumbers = sum
end function

|||workaround for that error
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1903450&SiteID=17|||

hi,

i have tried this but we cannot acess the textbox present in details section in footer.

this is the error

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

hemant

Distinct question

Hi
Ive got a problem with the Distinct function
I need to only select one column with the distinct function and leave the
rest so they can have duplicates.
For exampel
Table_Orders
OrderID | Orderdate | User | City
Then I want to select the unique OrderID to list all current orders but at
the same time i want the users and the orderdates no to be unique. so i want
the distinct function to work only on the OrderID column.
Is that possible ?
/GustafThis has been responded to in the .programming newsgroup.
Please do not multi-post.
Thanks!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gurra" <gurgel@.telia.com> wrote in message
news:umLGpRPnDHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi
> Ive got a problem with the Distinct function
> I need to only select one column with the distinct function and leave the
> rest so they can have duplicates.
> For exampel
> Table_Orders
> OrderID | Orderdate | User | City
> Then I want to select the unique OrderID to list all current orders but at
> the same time i want the users and the orderdates no to be unique. so i
want
> the distinct function to work only on the OrderID column.
> Is that possible ?
> /Gustaf
>
>

Distinct on Text Column

How can I select distinct values from a table which has column datatype as
Text.
data sholud n't be truncated.
--
Regards,
Kassim.http://support.microsoft.com/kb/162032/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
> How can I select distinct values from a table which has column datatype as
> Text.
> data sholud n't be truncated.
> --
> Regards,
> Kassim.|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||Can you post some DDL and your query please.
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:517D91E0-FA6D-41EB-AD5C-3CAF4ED0F393@.microsoft.com...
>I do get these error, is there any other way to over come this.
> Kassim.
> "Jens Smeyer" wrote:
>|||Does the text column need to be part of the DISTINCT
operator, or can you be sure the text columns match if all
the other columns match? If you only need DISTINCT on
the other columns, one solution is to create a primary key or
unique column for the table. If myID is a unique
integer column, you could do something like this:
select * from myTable
where myID in (
select min(myID)
from myTable
group by col1, col2, col3
-- Do *not* include the text column in this list
)
If there are two different text column values for
the same (col1, col2, col3), you will get only one
of those rows.
If you need to determine if the text columns are unequal,
you could compare the first 8000 characters, or more if
you want:
select * from myTable
where not exists (
select * from myTable as Tcopy
where Tcopy.col1 = T.col1
and Tcopy.col2 = T.col2
..
and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
and Tcopy.myID < T.myID
)
Steve Kass
Drew University
M Kassim wrote:

>How can I select distinct values from a table which has column datatype as
>Text.
>data sholud n't be truncated.
>|||Hi,
I have a table called comments, which has 2 columns commentID primarykey
and comment [Text datatype], now I would like
select distinct comment from comments.
Kassim.
---
"Steve Kass" wrote:

> Does the text column need to be part of the DISTINCT
> operator, or can you be sure the text columns match if all
> the other columns match? If you only need DISTINCT on
> the other columns, one solution is to create a primary key or
> unique column for the table. If myID is a unique
> integer column, you could do something like this:
> select * from myTable
> where myID in (
> select min(myID)
> from myTable
> group by col1, col2, col3
> -- Do *not* include the text column in this list
> )
> If there are two different text column values for
> the same (col1, col2, col3), you will get only one
> of those rows.
> If you need to determine if the text columns are unequal,
> you could compare the first 8000 characters, or more if
> you want:
> select * from myTable
> where not exists (
> select * from myTable as Tcopy
> where Tcopy.col1 = T.col1
> and Tcopy.col2 = T.col2
> ...
> and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
> and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
> and Tcopy.myID < T.myID
> )
> Steve Kass
> Drew University
> M Kassim wrote:
>
>sql

Distinct on single column?

Hi,

This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.

SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d

INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p ON p.code = sol.part

WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0

ORDER BY (p.code), datum DESC

The problem with this is that it returns multiple lines for every product (p.code). Like so:

code description price quantity total date

603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00

603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00

603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00

603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00

603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00

603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00

603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00

I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved

edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.

Hello

Just a few ideas, do not have an MSSQL instace nearby to test:

1) use cursor, which I'd prefer to avoid

- declare cursor for "Select Distinct (code) From EfasLive..part"

- for each cursor value do the select on joined tables where date = MAx(date) to get the most recent value

2) do something like

Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)

The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)

3) try to use CTEs (Common Table Expressions)

Post the solution after you find one! Tnx
|||

Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me I'll most definitely look into CTEs and post my findings or a solution.

edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE Even then I'd have no clue how to drop the older records The only technique I know is to group them but then you'd have to use MAX or COUNT or AVG or whatever .. and then I wouldn't have the correct price and/or date. So you wouldn't realy be dropping them.

I'll look into the pointer thing.

|||

Here the query,

Code Snippet

;With CTE

as

(

SELECT DISTINCT

p.code

, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description

, sol.p_sales as price

, sol.q_ordered as quantity

, sol.p_sales * sol.q_ordered as total

, so.date_in as date

, max(so.date_in) over(partition by p.code) as maxdate

--, Row_Number() over(partition by p.code order by so.date_in desc) rid

FROM

EfasLive..debtor AS d

INNER JOIN Informatica..so AS so

ON so.deb_code = d.code

AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol

ON sol.code = so.code

AND sol.co_code = so.co_code

AND sol.acc_year = so.acc_year

AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p

ON p.code = sol.part

WHERE

d.[grp{003}] = 'GROUP'

AND p.co_code = 1

AND p.code NOT LIKE '&%'

AND so.date_in > DATEADD(m,-3,GETDATE())

AND sol.q_ordered > 0

)

Select

code

, description

, price

, quantity

, total

, date

From

CTE

Where

date = maxdate

--rid=1

Distinct on only one column, return all columns

I have a table with many columns. I want to return all columns, and I want
only the first record for each distinct value in the salutation column. I
come up with the following:
select c1.* from complainer as c1
join (select distinct salutation from complainer) as c2
on c1.complainerid = c2.complainerid
The code above returns the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'complainerid'.
Suggestions greatly appreciated.
RandyHow do you determine "first"? I'm assuming that you are using an
IDENTITY column as your id, so try something like this:
select c1.* from complainer as c1
join (select complainerid = MIN(complainerid), salutation
from complainer GROUP BY salutation) as c2
on c1.complainerid = c2.complainerid
HTH,
Stu

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.

DISTINCT on a single column

Hi,

I have a table of say 7 columns. I need to select all the columns but the DISTINCT clause should apply only to one column.

Example:

Name......ID

John.......1
John.......2
Mary.......3

I need only one record for John. But both Name and ID should be selected.

Thanks"I need only one record for John. But both Name and ID should be selected."

What value do you expect to be present in the ID field, given your example data?|||Any value in the ID field can be selected. It's only the name that matters.|||???

So what result to you want from:

John.......1
John.......2
Mary.......3

Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

Option C:
John, 1
, 2
Mary, 3

Your logic is not clear.

blindman|||Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

I can do with either Option A or Option B. what ID is selected with John is immaterial. I don't want John to be reapeated. That's all.

Thanks|||Can you provide the DDL for the table .. That might help a lot .. is the id an identity or unique column ?|||Distinct is not the correct function to use here. Distinct eliminates any duplicate rows. It acts on the entire row not just a column.

Try using Limit or TOP|||You want a group by query.

Select Name, Min(ID)
from YourTable
Group By Name

blindman|||What's "Limit"?

blindman|||Here is the acual data

Name......ID...Dept..University...

John......17...A........XYZ
John......18...B........XYZ

Now if I need only one John. Any record would do. How do I use the GROUP BY. What is this min function? My data has ID as char.

Thanks|||i think "limit" is in mysql and not in mssql ... wrong forum buddy|||Assuming ID is a unique value in your table (it better be, or your logic is not possible):

select YourTable.*
from YourTable
inner join
(select Name,
Min(ID) ID
from YourTable) DistinctIDs
on YourTable.ID = DistinctIDs.ID

This selects a single ID for each unique name and returns the data associated with that ID.

blindman|||"select Name,
Min(ID) ID
from YourTable"

Doesn't a Group By clause need to be here.|||Yeah, that would probably help... :rolleyes:

blindman

Distinct Count on non-numeric column AS 2000

I have a cube with a fact table containing figures as movements within months, therefore duplicating the associated references.

I need to be able to count the distinct references (text column), but in AS 2000 I cannot get a correct answer.

If I do a distinctcount on the Members of the dimension I get a lower count than there actually is.

I have tried placing the distinct references in a separate table joined to the fact table on the reference and then counting them, but that just gives me the total figure all the time. If I use distinctcount I get the same answer as using distinct count on the dimension, i.e. wrong!

This is very frustrating - does anyone have any ideas?

I know it would be fine in As2005 but we cannot upgrade just yet.

Thank you

Try to create a table in SQL with an identity column for each reference.

Change the way the fact tavle is loaded to the cube (may be using a view) and do the DistinctCount over the newly created column.

Hope it helps.

|||

Thanks for your suggestion.

I already have a table containing all the distinct references, which I build from the fact table (which is a proper table, not a view - I have encountered problems with counts when using views before!) link to the fact table by the reference and then create a hidden dimension with one level, the reference; it is the DistinctCount of these members which is coming out wrong, although when you look at the count of the dimension level it is correct.

Therefore I added an id column to this table and a corresponding level to the dimension and tried a DistinctCount of the Descendants of the dimension at the id level, but am still getting the incorrect count. I have looked at examples of the individual references that it is failing to count but cannot see any reason why.

|||

One question: are you doing the DistinctCount in the relational or in the OLAP?

If you do the DistinctCount in the relational and then feed it to the OLAP you must be aware that the sum of DistinctCounts is different from DistincCount of the sum.

I use views 99.9% of the time without problems.

My suggestion is to create a view for the facts to be able to return the id, the other metrics columns and all dimenstion columns. Then in the cube create the DistinctCount measure over the id column.

|||

I am now completely baffled by AS's inability to do a count.

As recommended I have added a numeric id column to the fact table relating to the reference I need to count, populated it, then created another cube as a copy of my original one but with only 1 measure, the DistinctCount of the ID column.

Even before I merge these 2 cubes into a virtual cube I can see that the count is STILL wrong - it shows in AS as 25538 whereas checking via SQL (i.e. select count(distinct ID) from FactCube) gives the correct answer of 25995.

This was the same incorrect number I was getting before when I tried a calculated member - where have the other 457 rows gone? I know of no other way to do this and really need some advice as to why AS appears unable to do a proper count. It is vital to the cube that this functionality is available.

We use Sybase as our relational database, in case this has any bearing on the matter.

Thank you

Rachel

|||

Hi,

I use Sybase IQ and little Sybase ASE as the source without problems. I think I had a problem with group by in IQ 12.6. However, it should not be relevant here.

Make sure the Relational query and OLAP query are comparable. Are you sure you do not have any other fact data, besides that query?

Get the select that AS2000 send to Sybase and analyze it. Then run it to compare the data.

DistinctCount is a very slow process, but in my experience accurate.

|||Thank you for that advice - I went through the SQL statement and eventually tracked down the problem to some dodgy data in one of the dimensions. Thank goodness for that!

Distinct Count Confusion

I have an issue with our cube. In the DW we have a column leadId. Performing a distinct count with no filters gives us about 18000 on that leadId. We have a measure using a Distinct Count on the leadId but the value only gives us about 15000 with no filters. Am I missing something with how the behavior of distinct count should act?Please, check queries that are issued from SSAS to the datasource database.|||

So I have found the reason the distinct values are being filtered, but I'm not sure what in our cube design is dictating the behavior to act this way. Here is the query issued to the DW... What confuses me is that planId is a nullable field. So I'm not sure why it would issue the statement like this. Any ideas?

SELECT [Facts_CurrentStatus].[Facts_CurrentStatusleadId0_0] AS [Facts_CurrentStatusleadId0_0],[Facts_CurrentStatus].[Facts_CurrentStatusapplicationId0_1] AS [Facts_CurrentStatusapplicationId0_1],[Facts_CurrentStatus].[Facts_CurrentStatusapplicationCustomerCount0_2] AS [Facts_CurrentStatusapplicationCustomerCount0_2],[Facts_CurrentStatus].[Facts_CurrentStatusapplicationPremium0_3] AS [Facts_CurrentStatusapplicationPremium0_3],[Facts_CurrentStatus].[Facts_CurrentStatuspolicyId0_4] AS [Facts_CurrentStatuspolicyId0_4],[Facts_CurrentStatus].[Facts_CurrentStatuspolicyCustomerCount0_5] AS [Facts_CurrentStatuspolicyCustomerCount0_5],[Facts_CurrentStatus].[Facts_CurrentStatuspolicyPremium0_6] AS [Facts_CurrentStatuspolicyPremium0_6],[Facts_CurrentStatus].[Facts_CurrentStatusleadArrivalToApplicationCompleteLagDays0_7] AS [Facts_CurrentStatusleadArrivalToApplicationCompleteLagDays0_7],[Facts_CurrentStatus].[Facts_CurrentStatusleadArrivalToPolicyIssueLagDays0_8] AS [Facts_CurrentStatusleadArrivalToPolicyIssueLagDays0_8],[Facts_CurrentStatus].[Facts_CurrentStatusapplicationCompleteToPolicyIssueLagDays0_9] AS [Facts_CurrentStatusapplicationCompleteToPolicyIssueLagDays0_9],[Facts_CurrentStatus].[Facts_CurrentStatusrateUp0_10] AS [Facts_CurrentStatusrateUp0_10],[Facts_CurrentStatus].[Facts_CurrentStatusapplicationOriginatedOnDate0_11] AS [Facts_CurrentStatusapplicationOriginatedOnDate0_11],[Facts_CurrentStatus].[Facts_CurrentStatussetByEmployeeId0_12] AS [Facts_CurrentStatussetByEmployeeId0_12],[Facts_CurrentStatus].[Facts_CurrentStatusutcDate0_13] AS [Facts_CurrentStatusutcDate0_13],[Facts_CurrentStatus].[Facts_CurrentStatuscarrierId0_14] AS [Facts_CurrentStatuscarrierId0_14],[Facts_CurrentStatus].[Facts_CurrentStatuspartnerId0_15] AS [Facts_CurrentStatuspartnerId0_15],[Facts_CurrentStatus].[Facts_CurrentStatusplanId0_16] AS [Facts_CurrentStatusplanId0_16],[Facts_CurrentStatus].[Facts_CurrentStatusleadOriginatedOnDate0_17] AS [Facts_CurrentStatusleadOriginatedOnDate0_17],[Facts_CurrentStatus].[Facts_CurrentStatuspolicyOriginatedOnDate0_18] AS [Facts_CurrentStatuspolicyOriginatedOnDate0_18],[Facts_CurrentStatus].[Facts_CurrentStatuspolicyIssuedOnDate0_19] AS [Facts_CurrentStatuspolicyIssuedOnDate0_19],[Facts_CurrentStatus].[Facts_CurrentStatuszipCodeId0_20] AS [Facts_CurrentStatuszipCodeId0_20],[Facts_CurrentStatus].[Facts_CurrentStatusapplicationCompletedOnDate0_21] AS [Facts_CurrentStatusapplicationCompletedOnDate0_21],[Facts_CurrentStatus].[Facts_CurrentStatusenrollerId0_22] AS [Facts_CurrentStatusenrollerId0_22],[Facts_CurrentStatus].[Facts_CurrentStatusagentId0_23] AS [Facts_CurrentStatusagentId0_23],[Facts_CurrentStatus].[Facts_CurrentStatuscustomerId0_24] AS [Facts_CurrentStatuscustomerId0_24],[Facts_CurrentStatus].[Facts_CurrentStatusstatusId0_25] AS [Facts_CurrentStatusstatusId0_25],[Facts_CurrentStatus].[Facts_CurrentStatuscampaignId0_26] AS [Facts_CurrentStatuscampaignId0_26],[Dimensions_Plan_20].[carrierId] AS [Dimensions_PlancarrierId3_0]

FROM

(

SELECT [leadId] AS [Facts_CurrentStatusleadId0_0],[applicationId] AS [Facts_CurrentStatusapplicationId0_1],[applicationCustomerCount] AS [Facts_CurrentStatusapplicationCustomerCount0_2],[applicationPremium] AS [Facts_CurrentStatusapplicationPremium0_3],[policyId] AS [Facts_CurrentStatuspolicyId0_4],[policyCustomerCount] AS [Facts_CurrentStatuspolicyCustomerCount0_5],[policyPremium] AS [Facts_CurrentStatuspolicyPremium0_6],DateDiff("d", leadOriginatedOnDate, applicationCompletedOnDate) AS [Facts_CurrentStatusleadArrivalToApplicationCompleteLagDays0_7],DateDiff("d", leadOriginatedOnDate, policyIssuedOnDate) AS [Facts_CurrentStatusleadArrivalToPolicyIssueLagDays0_8],DateDiff("d", applicationCompletedOnDate, policyIssuedOnDate) AS [Facts_CurrentStatusapplicationCompleteToPolicyIssueLagDays0_9],policyPremium - applicationPremium AS [Facts_CurrentStatusrateUp0_10],[applicationOriginatedOnDate] AS [Facts_CurrentStatusapplicationOriginatedOnDate0_11],[setByEmployeeId] AS [Facts_CurrentStatussetByEmployeeId0_12],[utcDate] AS [Facts_CurrentStatusutcDate0_13],[carrierId] AS [Facts_CurrentStatuscarrierId0_14],[partnerId] AS [Facts_CurrentStatuspartnerId0_15],[planId] AS [Facts_CurrentStatusplanId0_16],[leadOriginatedOnDate] AS [Facts_CurrentStatusleadOriginatedOnDate0_17],[policyOriginatedOnDate] AS [Facts_CurrentStatuspolicyOriginatedOnDate0_18],[policyIssuedOnDate] AS [Facts_CurrentStatuspolicyIssuedOnDate0_19],[zipCodeId] AS [Facts_CurrentStatuszipCodeId0_20],[applicationCompletedOnDate] AS [Facts_CurrentStatusapplicationCompletedOnDate0_21],[enrollerId] AS [Facts_CurrentStatusenrollerId0_22],[agentId] AS [Facts_CurrentStatusagentId0_23],[customerId] AS [Facts_CurrentStatuscustomerId0_24],[statusId] AS [Facts_CurrentStatusstatusId0_25],[campaignId] AS [Facts_CurrentStatuscampaignId0_26]

FROM [Facts].[CurrentStatus]

)

AS [Facts_CurrentStatus],[Dimensions].[Plan] AS [Dimensions_Plan_20]

WHERE

(

(

[Facts_CurrentStatus].[Facts_CurrentStatusplanId0_16] = [Dimensions_Plan_20].[surrogatePlanId] --Filters out the leads with no plan interest.

)

)

ORDER BY [Facts_CurrentStatus].[Facts_CurrentStatusleadId0_0]

ASC

|||

but I'm not sure what in our cube design is dictating the behavior to act this way.
Here is the query issued to the DW... What confuses me is that planId is a nullable field.

I don't know your cube design, therefore I can't say what is wrong.
My advice, try to avoid nullable field in the DW. It is not only my opinion.

|||Yes we already started to refactor the DW so the nullable fields would no longer exist. One thing though, I wasn't necessarily asking you to tell me how our design was wrong. I was more confused as to why AS would create a query that joins on a nullable field. Regardless, problem solved.

Dist. Partitioned Views

I've read that the partitioning column must be part or
all of the primary key.
Is this design advice or a requirement? If it's a
requirement, why?
Any and all help appreciated,
Thanks,
Andrewit is a requirement, so that sql server will know to which
server the new row belongs. This is just to avoid
confilict and overlapping of the data on multiple servers.
>--Original Message--
>I've read that the partitioning column must be part or
>all of the primary key.
>Is this design advice or a requirement? If it's a
>requirement, why?
>Any and all help appreciated,
>Thanks,
>Andrew
>.
>sql

Displaying the dates in a column.

Can someone help me with this. I've been trying to produce it this way but still unsuccessful.
I'd like my table to look like this:

Month: January

Day wkDay
-
1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
. .
. .
31 Wed

I want to display the whole month..
Please help..
Thanks..Hi,

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

select datepart(d, getdate()) 'Day',
case datepart(dw, getdate())
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
end 'wkDay'

BuNnY_MoOn wrote:

Can someone help me with this. I've been trying to produce it this way but still unsuccessful.
I'd like my table to look like this:

Month: January

Day wkDay
-
1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
. .
. .
31 Wed

I want to display the whole month..
Please help..
Thanks..

|||

you don't need a CASE statement left,3 and datename(dw) is enough

select datepart(d, getdate()) 'Day', left(datename(dw, getdate()) ,3)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks..

But i was sort of thinking of listing all the dates as per indicated in my example:

Day wkDay

1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
7 Sun
8 Mon
9 Tues
10 Wed
11 Thurs
12 Fri
13 Sat
14 Sun
. .
. .
31 Wed

not just one day..

|||

here you go

--first create a number table--do this only ONCE!!!!
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0

WHILE @.intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)

SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO

--now run this
SELECT datepart(dd,DATEADD(dd,numberID,GETDATE())) as Day,left(datename(dw,DATEADD(dd,numberID,GETDATE())),3) as DayName
FROM dbo.NumberPivot
WHERE NumberID < 100

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks a bunch Sql Menace! |||Create a calendar table and use it rather than writing code. It is much more flexible, robust and can handle more scenarios easily (different types of calendars - fiscal, yearly; holidays; language settings etc). Search the WWW for pointers on how to build a Calendar table.|||Thanks

Umachandar Jayachandran - MS.
I'll take that into consideration..

Displaying same column name in the same column

Hi, I'm trying to accomplish something with this code:

SELECT ProductionOrder.PO, ProductionOrder.Part, Single.Length,

Single.Date, Pairs.Length, Pairs.Date FROM [ProductionOrder]

LEFT OUTER JOIN Pairs ON Pairs.PO = [ProductionOrder].PO AND

Pairs.Part = [ProductionOrder].Part

LEFT OUTER JOIN Single ON Single.PO = [ProductionOrder].PO AND

Single.Part = [ProductionOrder].Part

WHERE (Single.Broke='True' AND (Single.[BrokeFixed] = 'False' OR Single.[BrokeFixed] IS NULL))

OR (Pairs.Broke='True' AND (Pairs.[BrokeFixed] = 'False' OR Pairs.[BrokeFixed] IS NULL))

With this I get the following result:

PO | Part | Length | Date | Length | Date

-

602520 | 3 | 24000 | 2007-08-24 15:33:33.727 | NULL | NULL
602521 | 3 | NULL | NULL | 14550 | 2007-08-29 17:41:01.930

But what I want is:

PO | Part | Length | Date

602520 | 3 | 24000 | 2007-08-24 15:33:33.727
602521 | 3 | 14550 | 2007-08-29 17:41:01.930

How can I accomplish this? Please, any help would be very much appreciated.

Thanks a lot in advance

You can use ISNULL or COALESCE.

eg SELECT ISNULL(Single.Date, Pairs.Date) AS Date, ISNULL(Single.Length, Pairs.Length) AS length

Remember, this method will always use Single.Date unless its NULL so if both are populated, then Single.Date will be displayed. This also won't check a dependancy on the Date and Length ie its possible that you could have a Single.Date with a Pairs.Length unless you are 100% sure this situation could never occur. If it might, you may want to consider using a CASE statement to choose which values to display.

Check CASE/ISNULL/COALESCE out in more detail in Books Online.

HTH!

|||

Thanks a lot! That's excatly what I needed. I'm not an expert T-SQL programmer so I was getting a hard time trying

to get this result. And no, they will never be both populated as there will never be the same PO number on both

Single and Pairs table. But both PO numbers will be on the ProductionOrder Table as all POs (ProductionOrders)

will be on the ProductionOrder table, but each PO has a corresponding table, defining its type with its details (Single, Pairs, Groups, and FinalProducts).

Thanks again. Both of you who tried to help me out.

Regards

Fábio

Displaying rows by month

Hi All,
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:

Jan 2003
(Rows whose date is in Jan 2003)
Feb 2003
(Rows whose date is in Feb2003)
.
.
.
Jan 2004
(Rows whose date is in Jan 2004)
Feb 2004
(Rows whose date is in Feb2004)
.
.
so on...

Can any body give my SQL query to get the desired results.
Thanks a lot,
Kumar.For January, 2003:

SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 1 AND YEAR(DateColumn) = 2003

For February, 2003:
SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 2 AND YEAR(DateColumn) = 2003

and so on...|||you need to use just simple order by this datetime field
if I understand your question

Displaying my report on a blackberry

I'm having a problem with displaying my report table on my blackberry. When i try to display anything it comes out in just one column not in a table format. Can anyone tell me if it is possible to display the whole table which is in pdf file format.

NVM i got it again. lol

Here's the answer:

Download beamberry at www.beamberry.com it allows u to viewPDF, Word, PPT, XLS, RTF, ZIP archives, etc. Its free so good luck. Also it is one of the best.

Displaying HTML within an Access Report

Is this possible? I have a column which has data with HTML formatting.
Is it possible to remove the tags within a Stored Procedure so that it
displays nicely in an Access Report?
This is my SP:
ALTER PROCEDURE dbo.sp_Phat_Beats
AS SELECT fldcat, flddescript, fldtracklisting, fldprice, fldCategory
FROM dbo.tblProducts
WHERE (fldprice <> 0) AND (fldCategory = 17)
ORDER BY fldcat
fldtracklisting is the field that is HTML formatted. Any help would be
relly appreciated.
SteveHi
You could use replace multiple times to remove each tag, but this would
require embeded calls to the procedure one call per string you wish to
replace which could prove tedious. Check out replace in Books online
John
"Dooza" wrote:

> Is this possible? I have a column which has data with HTML formatting.
> Is it possible to remove the tags within a Stored Procedure so that it
> displays nicely in an Access Report?
> This is my SP:
> ALTER PROCEDURE dbo.sp_Phat_Beats
> AS SELECT fldcat, flddescript, fldtracklisting, fldprice, fldCategory
> FROM dbo.tblProducts
> WHERE (fldprice <> 0) AND (fldCategory = 17)
> ORDER BY fldcat
> fldtracklisting is the field that is HTML formatted. Any help would be
> relly appreciated.
> Steve
>