| Groups | Blog | Home |
sql server mseq : distinct with all the columns
I've got something, but I can't get all of my columns shown. I've got a table with a few columns and in one columns there are records with duplicate values, so I use a distinct to get all the duplicates out of the result set, but this only works for 1 column, I also want to show the other columns of that record that was filtered by the distinct clause, but this doesn't work. Any suggestions?
Without having your table schema DDL, and some sample data, it is =
difficult to 'guess' the nature of your issue. However, using my weegee board -yes, that is how My weegee board is = spelled! You could use GROUP BY. (A bit heavy-handed, but hey, it works!) SELECT Column1 , Column2 , Column3 , Column4 FROM MyTable WHERE (Criteria here) GROUP BY=20 Column1 , Column2 , Column3 , Column4 If Column4 has multiple entries (all others being the same) there will = be only 1 row in the resultset. The same goes for any combinations, = starting from the rightmost listed column, where the columns have = duplicate values. This should work, and there may be other suggestions coming --=20 Arnie Rowland, YACE*=20 "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam [quoted text, click to view] "Philippe" <Philippe@discussions.microsoft.com> wrote in message =
news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... > Hi, >=20 > I've got something, but I can't get all of my columns shown. I've got = a=20 > table with a few columns and in one columns there are records with = duplicate=20 > values, so I use a distinct to get all the duplicates out of the = result set,=20 > but this only works for 1 column, I also want to show the other = columns of=20 > that record that was filtered by the distinct clause, but this doesn't = work. >=20 > Any suggestions? >=20
Something else.
If you had used a 'real' email address, instead of being so clever, I would have sent your responce directly to you and you would have saved some time. -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam [quoted text, click to view] "Philippe" <Philippe@discussions.microsoft.com> wrote in message news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... > Hi, > > I've got something, but I can't get all of my columns shown. I've got a > table with a few columns and in one columns there are records with > duplicate > values, so I use a distinct to get all the duplicates out of the result > set, > but this only works for 1 column, I also want to show the other columns of > that record that was filtered by the distinct clause, but this doesn't > work. > > Any suggestions? > > thanks
I tried this, but it didn't worked out.
This is my table: ordnr, brpr, magnr,date, The table contains orders, the ordnr contains the number of the order but it is not unique because an order consists out of more then one line, the total amount of the order is calculated and put in the first occurance (thus the first line) of an order with a specific number. eg: ordernumber 123 consists of 3 records like this: 123 125 1 01/01/2001 (*) 123 100 1 01/01/2001 123 25 1 01/01/2001 I want only the first occurance of that records out of my database in this case the * row. We tried everything with distinct and other possibilities but I'm afraid this isn't possible, or is it ? thanx [quoted text, click to view] "Arnie Rowland" wrote:
> Without having your table schema DDL, and some sample data, it is difficult to 'guess' the nature of your issue. > > However, using my weegee board -yes, that is how My weegee board is spelled! > > You could use GROUP BY. (A bit heavy-handed, but hey, it works!) > > SELECT > Column1 > , Column2 > , Column3 > , Column4 > FROM MyTable > WHERE (Criteria here) > GROUP BY > Column1 > , Column2 > , Column3 > , Column4 > > If Column4 has multiple entries (all others being the same) there will be only 1 row in the resultset. The same goes for any combinations, starting from the rightmost listed column, where the columns have duplicate values. > > This should work, and there may be other suggestions coming > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "Philippe" <Philippe@discussions.microsoft.com> wrote in message news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... > > Hi, > > > > I've got something, but I can't get all of my columns shown. I've got a > > table with a few columns and in one columns there are records with duplicate > > values, so I use a distinct to get all the duplicates out of the result set, > > but this only works for 1 column, I also want to show the other columns of > > that record that was filtered by the distinct clause, but this doesn't work. > > > > Any suggestions? > >
You may have a table design that will not allow you to retreive the
information you desire. In your situation (if the table has only the four columns you indicated), you have absolutely no way to discern which row is the 'first' row. And the database couldn't care less about which row is first -by design. I suggest you revisit the table design. As a ridiculous kludge you could add another column (or two or three) to indicate row sequence for the order. I recommend that you examine how orders are handled in the Northwind database (the sample test and learning database that comes with SQL Server and Access.) A more workable design is one where there is a Order header table (one row per order). That table contains the order number, date, customer id, etc. And then there is a OrderDetails table that contains the item by item information. It is 'related' to the Orders table with a Foreign key - Primary key relationship. So, if there are only the four columns you indicated, exactly where is the 'total amount of the order' placed? -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam [quoted text, click to view] "Philippe" <Philippe@discussions.microsoft.com> wrote in message news:2536268E-DF2D-4217-9E99-6FF50E5D4205@microsoft.com... >I tried this, but it didn't worked out. > > This is my table: > > ordnr, brpr, magnr,date, > > The table contains orders, the ordnr contains the number of the order but > it > is not unique because an order consists out of more then one line, the > total > amount of the order is calculated and put in the first occurance (thus the > first line) of an order with a specific number. > > eg: ordernumber 123 consists of 3 records like this: > 123 125 1 01/01/2001 (*) > 123 100 1 01/01/2001 > 123 25 1 01/01/2001 > > I want only the first occurance of that records out of my database in this > case the * row. We tried everything with distinct and other possibilities > but > I'm afraid this isn't possible, or is it ? > > thanx > > "Arnie Rowland" wrote: > >> Without having your table schema DDL, and some sample data, it is >> difficult to 'guess' the nature of your issue. >> >> However, using my weegee board -yes, that is how My weegee board is >> spelled! >> >> You could use GROUP BY. (A bit heavy-handed, but hey, it works!) >> >> SELECT >> Column1 >> , Column2 >> , Column3 >> , Column4 >> FROM MyTable >> WHERE (Criteria here) >> GROUP BY >> Column1 >> , Column2 >> , Column3 >> , Column4 >> >> If Column4 has multiple entries (all others being the same) there will be >> only 1 row in the resultset. The same goes for any combinations, starting >> from the rightmost listed column, where the columns have duplicate >> values. >> >> This should work, and there may be other suggestions coming >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "Philippe" <Philippe@discussions.microsoft.com> wrote in message >> news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... >> > Hi, >> > >> > I've got something, but I can't get all of my columns shown. I've got a >> > table with a few columns and in one columns there are records with >> > duplicate >> > values, so I use a distinct to get all the duplicates out of the result >> > set, >> > but this only works for 1 column, I also want to show the other columns >> > of >> > that record that was filtered by the distinct clause, but this doesn't >> > work. >> > >> > Any suggestions? >> > >> > thanks
Thanks, but changing the database schema is not an option because it's an ERP
system based on PROGRESS. We are working on another point of view, maybe this will help to get this stupid data to come out of this PROGRESS DB. thank for your suggestions... P [quoted text, click to view] "Arnie Rowland" wrote:
> You may have a table design that will not allow you to retreive the > information you desire. > > In your situation (if the table has only the four columns you indicated), > you have absolutely no way to discern which row is the 'first' row. And the > database couldn't care less about which row is first -by design. > > I suggest you revisit the table design. As a ridiculous kludge you could add > another column (or two or three) to indicate row sequence for the order. > > I recommend that you examine how orders are handled in the Northwind > database (the sample test and learning database that comes with SQL Server > and Access.) > > A more workable design is one where there is a Order header table (one row > per order). That table contains the order number, date, customer id, etc. > > And then there is a OrderDetails table that contains the item by item > information. It is 'related' to the Orders table with a Foreign key - > Primary key relationship. > > So, if there are only the four columns you indicated, exactly where is the > 'total amount of the order' placed? > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "Philippe" <Philippe@discussions.microsoft.com> wrote in message > news:2536268E-DF2D-4217-9E99-6FF50E5D4205@microsoft.com... > >I tried this, but it didn't worked out. > > > > This is my table: > > > > ordnr, brpr, magnr,date, > > > > The table contains orders, the ordnr contains the number of the order but > > it > > is not unique because an order consists out of more then one line, the > > total > > amount of the order is calculated and put in the first occurance (thus the > > first line) of an order with a specific number. > > > > eg: ordernumber 123 consists of 3 records like this: > > 123 125 1 01/01/2001 (*) > > 123 100 1 01/01/2001 > > 123 25 1 01/01/2001 > > > > I want only the first occurance of that records out of my database in this > > case the * row. We tried everything with distinct and other possibilities > > but > > I'm afraid this isn't possible, or is it ? > > > > thanx > > > > "Arnie Rowland" wrote: > > > >> Without having your table schema DDL, and some sample data, it is > >> difficult to 'guess' the nature of your issue. > >> > >> However, using my weegee board -yes, that is how My weegee board is > >> spelled! > >> > >> You could use GROUP BY. (A bit heavy-handed, but hey, it works!) > >> > >> SELECT > >> Column1 > >> , Column2 > >> , Column3 > >> , Column4 > >> FROM MyTable > >> WHERE (Criteria here) > >> GROUP BY > >> Column1 > >> , Column2 > >> , Column3 > >> , Column4 > >> > >> If Column4 has multiple entries (all others being the same) there will be > >> only 1 row in the resultset. The same goes for any combinations, starting > >> from the rightmost listed column, where the columns have duplicate > >> values. > >> > >> This should work, and there may be other suggestions coming > >> -- > >> Arnie Rowland, YACE* > >> "To be successful, your heart must accompany your knowledge." > >> > >> *Yet Another Certification Exam > >> > >> > >> "Philippe" <Philippe@discussions.microsoft.com> wrote in message > >> news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... > >> > Hi, > >> > > >> > I've got something, but I can't get all of my columns shown. I've got a > >> > table with a few columns and in one columns there are records with > >> > duplicate > >> > values, so I use a distinct to get all the duplicates out of the result > >> > set, > >> > but this only works for 1 column, I also want to show the other columns > >> > of > >> > that record that was filtered by the distinct clause, but this doesn't > >> > work. > >> > > >> > Any suggestions? > >> > > >> > thanks > >
Is the total amount of the order always has bigest brpr within that order?
If it does, you can try following select distinct ordnr, (select max(brpr) from Order where Ordnr = or.Ordnr and magnr = or.magnr and date = or.date) as brpr, magnr, date from Order or [quoted text, click to view] "Philippe" <Philippe@discussions.microsoft.com> wrote in message news:C0D3F28F-2424-42B7-A9F6-C46D81530962@microsoft.com... > Thanks, but changing the database schema is not an option because it's an ERP > system based on PROGRESS. > > We are working on another point of view, maybe this will help to get this > stupid data to come out of this PROGRESS DB. > > thank for your suggestions... > > P > "Arnie Rowland" wrote: > > > You may have a table design that will not allow you to retreive the > > information you desire. > > > > In your situation (if the table has only the four columns you indicated), > > you have absolutely no way to discern which row is the 'first' row. And the > > database couldn't care less about which row is first -by design. > > > > I suggest you revisit the table design. As a ridiculous kludge you could add > > another column (or two or three) to indicate row sequence for the order. > > > > I recommend that you examine how orders are handled in the Northwind > > database (the sample test and learning database that comes with SQL Server > > and Access.) > > > > A more workable design is one where there is a Order header table (one row > > per order). That table contains the order number, date, customer id, etc. > > > > And then there is a OrderDetails table that contains the item by item > > information. It is 'related' to the Orders table with a Foreign key - > > Primary key relationship. > > > > So, if there are only the four columns you indicated, exactly where is the > > 'total amount of the order' placed? > > -- > > Arnie Rowland, YACE* > > "To be successful, your heart must accompany your knowledge." > > > > *Yet Another Certification Exam > > > > > > "Philippe" <Philippe@discussions.microsoft.com> wrote in message > > news:2536268E-DF2D-4217-9E99-6FF50E5D4205@microsoft.com... > > >I tried this, but it didn't worked out. > > > > > > This is my table: > > > > > > ordnr, brpr, magnr,date, > > > > > > The table contains orders, the ordnr contains the number of the order but > > > it > > > is not unique because an order consists out of more then one line, the > > > total > > > amount of the order is calculated and put in the first occurance (thus the > > > first line) of an order with a specific number. > > > > > > eg: ordernumber 123 consists of 3 records like this: > > > 123 125 1 01/01/2001 (*) > > > 123 100 1 01/01/2001 > > > 123 25 1 01/01/2001 > > > > > > I want only the first occurance of that records out of my database in this > > > case the * row. We tried everything with distinct and other possibilities > > > but > > > I'm afraid this isn't possible, or is it ? > > > > > > thanx > > > > > > "Arnie Rowland" wrote: > > > > > >> Without having your table schema DDL, and some sample data, it is > > >> difficult to 'guess' the nature of your issue. > > >> > > >> However, using my weegee board -yes, that is how My weegee board is > > >> spelled! > > >> > > >> You could use GROUP BY. (A bit heavy-handed, but hey, it works!) > > >> > > >> SELECT > > >> Column1 > > >> , Column2 > > >> , Column3 > > >> , Column4 > > >> FROM MyTable > > >> WHERE (Criteria here) > > >> GROUP BY > > >> Column1 > > >> , Column2 > > >> , Column3 > > >> , Column4 > > >> > > >> If Column4 has multiple entries (all others being the same) there will be > > >> only 1 row in the resultset. The same goes for any combinations, starting > > >> from the rightmost listed column, where the columns have duplicate > > >> values. > > >> > > >> This should work, and there may be other suggestions coming > > >> -- > > >> Arnie Rowland, YACE* > > >> "To be successful, your heart must accompany your knowledge." > > >> > > >> *Yet Another Certification Exam > > >> > > >> > > >> "Philippe" <Philippe@discussions.microsoft.com> wrote in message > > >> news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... > > >> > Hi, > > >> > > > >> > I've got something, but I can't get all of my columns shown. I've got a > > >> > table with a few columns and in one columns there are records with > > >> > duplicate > > >> > values, so I use a distinct to get all the duplicates out of the result > > >> > set, > > >> > but this only works for 1 column, I also want to show the other columns > > >> > of > > >> > that record that was filtered by the distinct clause, but this doesn't > > >> > work. > > >> > > > >> > Any suggestions? > > >> > > > >> > thanks > > > > > >
Looks good, but considering that it had to go so quick, we have used
Integration services to cleanse the data added some calculatons. This has fixed the problem :-) Thanks anyway :-) [quoted text, click to view] "Yong Zou (SearchIgnite)" wrote:
> Is the total amount of the order always has bigest brpr within that order? > If it does, you can try following > > select distinct ordnr, > (select max(brpr) from Order where Ordnr = or.Ordnr and magnr = > or.magnr and date = or.date) as brpr, > magnr, > date > from Order or > > "Philippe" <Philippe@discussions.microsoft.com> wrote in message > news:C0D3F28F-2424-42B7-A9F6-C46D81530962@microsoft.com... > > Thanks, but changing the database schema is not an option because it's an > ERP > > system based on PROGRESS. > > > > We are working on another point of view, maybe this will help to get this > > stupid data to come out of this PROGRESS DB. > > > > thank for your suggestions... > > > > P > > "Arnie Rowland" wrote: > > > > > You may have a table design that will not allow you to retreive the > > > information you desire. > > > > > > In your situation (if the table has only the four columns you > indicated), > > > you have absolutely no way to discern which row is the 'first' row. And > the > > > database couldn't care less about which row is first -by design. > > > > > > I suggest you revisit the table design. As a ridiculous kludge you could > add > > > another column (or two or three) to indicate row sequence for the order. > > > > > > I recommend that you examine how orders are handled in the Northwind > > > database (the sample test and learning database that comes with SQL > Server > > > and Access.) > > > > > > A more workable design is one where there is a Order header table (one > row > > > per order). That table contains the order number, date, customer id, > etc. > > > > > > And then there is a OrderDetails table that contains the item by item > > > information. It is 'related' to the Orders table with a Foreign key - > > > Primary key relationship. > > > > > > So, if there are only the four columns you indicated, exactly where is > the > > > 'total amount of the order' placed? > > > -- > > > Arnie Rowland, YACE* > > > "To be successful, your heart must accompany your knowledge." > > > > > > *Yet Another Certification Exam > > > > > > > > > "Philippe" <Philippe@discussions.microsoft.com> wrote in message > > > news:2536268E-DF2D-4217-9E99-6FF50E5D4205@microsoft.com... > > > >I tried this, but it didn't worked out. > > > > > > > > This is my table: > > > > > > > > ordnr, brpr, magnr,date, > > > > > > > > The table contains orders, the ordnr contains the number of the order > but > > > > it > > > > is not unique because an order consists out of more then one line, the > > > > total > > > > amount of the order is calculated and put in the first occurance (thus > the > > > > first line) of an order with a specific number. > > > > > > > > eg: ordernumber 123 consists of 3 records like this: > > > > 123 125 1 01/01/2001 (*) > > > > 123 100 1 01/01/2001 > > > > 123 25 1 01/01/2001 > > > > > > > > I want only the first occurance of that records out of my database in > this > > > > case the * row. We tried everything with distinct and other > possibilities > > > > but > > > > I'm afraid this isn't possible, or is it ? > > > > > > > > thanx > > > > > > > > "Arnie Rowland" wrote: > > > > > > > >> Without having your table schema DDL, and some sample data, it is > > > >> difficult to 'guess' the nature of your issue. > > > >> > > > >> However, using my weegee board -yes, that is how My weegee board is > > > >> spelled! > > > >> > > > >> You could use GROUP BY. (A bit heavy-handed, but hey, it works!) > > > >> > > > >> SELECT > > > >> Column1 > > > >> , Column2 > > > >> , Column3 > > > >> , Column4 > > > >> FROM MyTable > > > >> WHERE (Criteria here) > > > >> GROUP BY > > > >> Column1 > > > >> , Column2 > > > >> , Column3 > > > >> , Column4 > > > >> > > > >> If Column4 has multiple entries (all others being the same) there > will be > > > >> only 1 row in the resultset. The same goes for any combinations, > starting > > > >> from the rightmost listed column, where the columns have duplicate > > > >> values. > > > >> > > > >> This should work, and there may be other suggestions coming > > > >> -- > > > >> Arnie Rowland, YACE* > > > >> "To be successful, your heart must accompany your knowledge." > > > >> > > > >> *Yet Another Certification Exam > > > >> > > > >> > > > >> "Philippe" <Philippe@discussions.microsoft.com> wrote in message > > > >> news:39341A08-7912-4DB0-85FD-940F51BD8C40@microsoft.com... > > > >> > Hi, > > > >> > > > > >> > I've got something, but I can't get all of my columns shown. I've > got a > > > >> > table with a few columns and in one columns there are records with > > > >> > duplicate > > > >> > values, so I use a distinct to get all the duplicates out of the > result > > > >> > set, > > > >> > but this only works for 1 column, I also want to show the other > columns > > > >> > of > > > >> > that record that was filtered by the distinct clause, but this > doesn't > > > >> > work. > > > >> > > > > >> > Any suggestions? > > > >> > > > > >> > thanks > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |