Distinct for different columns

Hi everebody.
I need your advice, suppose i have 4 colums
col1, col2, col3, col3
e.g.
col1 | col2 | col3 | col4 |
aa | 1 | 3 | 4 |
aa | 1 | 2 | 1 |
bb | 2 | 2 | 1 |
cc | 3 | 2 | 1 |
so on ...
i need to distinct col2 so i my result set will be
col1 | col2 | col3 | col4 |
aa | 1 | 3 | 4 |
bb | 2 | 2 | 1 |
cc | 3 | 2 | 1 |
it's not important which row not display
any idea' thanks in advance
Message posted via http://www.webservertalk.comWhat is the Primary Key? Please post proper DDL rather than sketches of
tables, otherwise we can only guess. Here's my guess:
SELECT DISTINCT col1, col2, col3, col4
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM
(SELECT TOP 1 col1, col2, col3, col4
FROM YourTable
WHERE col2 = T1.col2
ORDER BY col1, col2, col3, col4) AS T2
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col4 = T2.col4)
Depending on your key there's probably a better way. However, I'm
always suspicious of requirements that say "show me some row, I don't
care which". To me, this indicates that the data model is incorrect -
if all rows of a set are equally valid then apparently the table is
carrying redundant data, which ought to be eliminated.
David Portas
SQL Server MVP
--|||Thanks, form now u can play lotto, because u r guessed right.
However can u please explain me your examle, i'm not really understand it.
Thanks
Message posted via http://www.webservertalk.com|||SELECT DISTINCT col1, col2, col3, col4
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM
(SELECT TOP 1 col1, col2, col3, col4
/* Get one row (TOP) for each value of
col2 in the outer (T1) query */
FROM YourTable
WHERE col2 = T1.col2
ORDER BY col1, col2, col3, col4) AS T2
WHERE T1.col1 = T2.col1
/* Is the row in T1 the same as the one
we got from the TOP subquery? */
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col4 = T2.col4)
David Portas
SQL Server MVP
--|||Thanks i got it .
P.S. the SELECT was from view and view have a lot of UNION from differents
tables, beacuse of that idon't told u what the primary key
Message posted via http://www.webservertalk.com|||Is it possible to perform exactly this funcionality without the 2 select
statements ?
Message posted via http://www.webservertalk.com|||In that case you'll probably find it much more efficient to do a join
between the base tables rather than query the view.
David Portas
SQL Server MVP
--|||>From my first post: "Please post proper DDL rather than sketches of
tables, otherwise we can only guess."
See: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I understand, but i need to use it a lot of times, that why i decided to
use view, it's big view and after i perform a lot of WHERE ... on this view
Message posted via http://www.webservertalk.com

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 Remainders

I need to show the whole number and remainder each as wole numbers. I have been trying to use modulo on the advice from a friend, but I don't understand this function.

More of an exlpanition: I am working with inventory and shipping quantities. If a pallet consists of 9 cases, and I have an order for 20 cases, I need to display in a report 2 pallets and 2 cases. These numbers can be in the same column or different columns...at this point I just need to be able to display the numbers.

Is there any way to do this? Any help would be appreciated.

-Thanks

P.S.

I posted this in the general forums before I realized I was there...not sure how to move posts, so I double-posted....sorry.

Hello,

Try this:

=cStr(cInt(Fields!CasesOrdered.Value / Fields!CasesOnPallet.Value)) + " Pallets " + cStr(Fields!CasesOrdered.Value Mod Fields!CasesOnPallet.Value) + " Cases"

Hope this helps.

Jarret

|||

This is what I need. Thanks

On this solution, how do I force a round down on the "Pallet" number (18.66667 is showing as 19 but needs to show as 18)?

I tried this:

=cStr(Floor(cInt(Fields!CasesOrdered.Value / Fields!CasesOnPallet.Value))) + " Pallets " + cStr(Fields!CasesOrdered.Value Mod Fields!CasesOnPallet.Value) + " Cases"

But this didn't change anything.

What are your thoughts?

-EDIT-

I used the cInt to remove the decimals

=cStr(Floor(cInt(Fields!CasesOrdered.Value / Fields!CasesOnPallet.Value))) + " Pallets " + cStr(cInt(Fields!CasesOrdered.Value Mod Fields!CasesOnPallet.Value)) + " Cases

|||

Try this:

=cStr(Floor(Fields!CasesOrdered.Value / Fields!CasesOnPallet.Value)) + " Pallets " + cStr(cInt(Fields!CasesOrdered.Value Mod Fields!CasesOnPallet.Value)) + " Cases

I should have used the Floor function on the Pallets calculation (instead of cInt). Then, the cInt on the Cases will remove the decimals.

Hope this helps.

Jarret

|||Perfect! Everything works exactly like is should now. Thanks for your henp.