Selecting Single Sorted Row From Many Column
Jul 23, 2005
Hi
Been at this for 2 days now.
Each business has several packages which they can sort using
sort_order.
I'm trying to get one package for each business(that I can do), however
I want it to be the one with the lowest sort_order value
As you can see below the first record has sort_order=5 when it should
be 1.
Most of the sort_order columns will be zero by default
Any help so i can get on with my life!
Cheers
Gary
------------Current select-------------------
SELECT *
FROM dbo.testAccommodation_Packages T1
WHERE (NOT EXISTS
(
SELECT *
FROM testAccommodation_Packages
WHERE business_id = T1.business_id AND Package_ID < T1.Package_ID)
)
--------------results:-----------------------
Package_IDbusiness_iditem_namesort_order
123rd Night FREE ...5
113Donegal Town ... 0
204Executive ...0
--------------To recreate----------------------
CREATE TABLE [testAccommodation_Packages] (
[Package_ID] [int] IDENTITY (1, 1) NOT NULL ,
[business_id] [int] NULL ,
[Item_Name] [nvarchar] (300) NOT NULL ,
[sort_order] [int] NULL CONSTRAINT
[DF_Accommodation_Packages_sort_order] DEFAULT (0),
)
-------------------------------------------------
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 Euro
PPS)','5')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1
Dinner Only € 129 PPS','4')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149
Euro PPS','3')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D
€169pps','2')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5
course Lunch 159 Euros PPS (~109 Stg)','1')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('6','2','April Pamper Package - 2 Night Special ONLY
€195pps','10')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only € 119
PPS','7')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner
€229pps','8')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner
€169pps','9')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('10','2','STAY SUNDAY NIGHTS FOR €25PPS','6')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B
','0')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55
Stg) Double Room','0')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner
109.00euros pps','0')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four course
gourmet dinner 99Euro PPS','0')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B
€129.00p.p.s.','0')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner €
139 PPS','0')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('21','4','Watercolour Painting Break 3 B&B Full Board and
Tuition € 335 PPS','0')
View 1 Replies
ADVERTISEMENT
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Nov 16, 2001
I have a query that returns a certain amount of results. How do I select the last row??? I know I can do the top row by doing (top 1). Basically I need the complete opposite. Please help...
I looked around and couldn't find anything on it.
thanks in advance
View 2 Replies
View Related
Jan 5, 2008
I want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!
View 1 Replies
View Related
Jul 17, 2015
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row asÂ
  cc  Zz  bab
Instead I am gettingÂ
 cc  Zz  rrrÂ
which is wrong
View 8 Replies
View Related
Jan 9, 2008
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
View 5 Replies
View Related
May 29, 2008
Hi
I have a gridview with a sql data source and a few drop down lists where i choose values to sorth what i would like to retrive from the table. The problem im facing is that when i pass values to the database, i can sort out and retrive my items when both my listboxes have selected values.
But when i want to select everything from the table where the column = clumn or cloumn = value i dont get anything back.
I have 2 dropdown lists. One for category and one for location. These are populated drom the database by selecting the column and retriving destinct values. I have a function that fires when one of the dropdown lists are changed witch changes the sql datasource select value so it retrives items sorted by the selected categories and locations.
Here is the function:
protected void DropDownList_Change(object sender, EventArgs e) { string Category = DropDownListKategori.SelectedValue.ToString(); string Location = DropDownListEtabl.SelectedValue.ToString(); SqlDataSource1.SelectCommand = "SELECT ARTNR, ARTTYP, AKTIVITET, DATUM, KUND, PLATS, KOMMENTAR FROM ARTIKEL WHERE ARTTYP = @ARTTYP AND PLATS = @PLATS"; SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add(@"ARTTYP", Category); SqlDataSource1.SelectParameters.Add(@"PLATS", Location); SqlDataSource1.DataBind(); }
The dropdown lists have an option to not retrive values by category or location. I have set that value to PLATS and ARTTYP thinking the query would would retrive everything if these were selected
SELECT ARTNR, ARTTYP, AKTIVITET, DATUM, KUND, PLATS, KOMMENTAR FROM ARTIKEL WHERE ARTTYP = ARTTYP AND PLATS = PLATS
however if both these drop down lists are set to not filter, my query gets nothing in return. And if one of the lists has this selected and ther other one has a value, say a location or something nothing is retrived either. However if both columns have something selected i do get values returned.
My speculation is that the query beeing run when this happens is that it is trying to find columns having the value ARTTYP or PLATS (category or location), as a string and actually seeing it as Im trying to retrive values where column = column.
Any suggestions on how i can make my query run as intended or is there another problem Im not seeing.
View 7 Replies
View Related
Feb 17, 2004
I want to know how to select a Column without selecting a column name
View 6 Replies
View Related
Sep 14, 2007
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
View 1 Replies
View Related
Feb 27, 2008
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
View 10 Replies
View Related
Aug 14, 2007
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
View 1 Replies
View Related
Aug 4, 2006
Hi,
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
Many thanks
Rob Gibson
View 5 Replies
View Related
Jun 5, 2006
Mike writes "Hi,
I am a beginner with TSQL and I hope this is not a silly question :-)
Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char.
EG:
ID ANIMAL
---------
1 CAT
2 DOG
3 PIG
4 RAT
5 PIG
6 DOG
7 DOG
.
.
And so on with many entries
How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times
EG:
From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once.
In my real life situation I have unknown numeric data in field 2 but the principal is the same.
How do I do this?
Thanks in Advance
Mike"
View 2 Replies
View Related
Jul 23, 2005
Hi :From a crystal report i get a list of employee firstnames as a stringinto my store procedure. Why is it comming this way ? hmmmmmm it's aquestion for me too.ex: "e1,e2,e3"here are my tablestblProjectsProjectId123tblEmployeeemployeeId FirstName1 e12 e23 e3tblProjectsToEmployeeProjectId employeeId1 11 21 32 12 23 13 23 34 14 3i need to find out the project ids all 3 of these employees worked on.so the out put i need isprojectId13How can i get it ????????????now i can use replace command to format it to a OR clause or ANDclauseSET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' ORemployeeId = ''') + ''''some thing like this.OR clause will give me all 4 projects.in('e1','e2','e3') will give me all 4 projects.of cause AND command will not give me any.other method i tried was adding the employee table 3 times into thesame SQL string and doing some thing likeWHERE (empTable1.Firstname ='e1' AND (empTable1.Firstnamein('e2','e3'))AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))AND ...and goes alone. this gives me some what i needed. but it's a verymessy way of doing it, because i get a comma seperated stringparameter i have to construct the sql string on the fly.any help or direction on this matter would greatly appreciated.thankseric
View 3 Replies
View Related
May 4, 2007
Greetings,
I have a dataset something like this:
Loc Value Time
A 3 1AM
A 8 2AM
A 2 3AM
A 1 4AM
...
B 8 1AM
B 1 2AM
...
When we collapse the Loc group I want to show the Max vlaue and the time it
occurred.
A 8 2AM
B 8 1AM
What is the expression for the Time column of the table to ensure
the time of the maximum value is shown?
Thanks
View 8 Replies
View Related
Feb 19, 2004
Hello,
Is it possible to get a list of rows from Multiple tables which have the same Column Name. I have 3 tables which comtain similar info
and I want to get a list of Names
the structure is
ID;Name;Address;Phone No.
I was thinking something along the lines of SELECT Name FROM TABLE1,TABLE2, TABLE3
But this does not work.
Is there a nice way of doing this with SQL or should I do code outside the SQL DB
View 4 Replies
View Related
Aug 3, 2006
I am migrating between SQL Server 2000 and SQL Server 2005 but hit a snag when attempting to write a query to display the column's description.
I used this code with SQL Server 2000 to get the "Description" data.
select o.name 'table' , c.name 'column' , p.value as 'description' , t.name 'datatype' , c.isnullable 'nullable?' , c.length, m.text 'default_text' from sysobjects o
join syscolumns c on o.id = c.id
join sysproperties p on o.id = p.id and p.smallid = c.colid
join systypes t on c.xtype = t.xtype
left outer join syscomments m on m.id = c.cdefault
order by 'table' , 'column'
How can I reproduce this with SQL Server 2005? I tried using the following which gives me a lot of the same data but not "Description":
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Any help here would be greatly appreciated.
View 3 Replies
View Related
Apr 17, 2008
Hi,
I am having two tables Products and Transaction
In products I have ProductID and Description. (10 Records)
In Transaction I have ProductID, Lot, Quantity and ListID . ( 4 Million Records)
When I use the inner join between these tables as below query its taking lot of time to give output.
select ProductID, Desc, Lot, qty from Products inner join Transactions on Products.ProductID = Transactions.ProductID
where ListID = '9090909'
otherwise, if I use the below query its takes very few milli seconds to give output but I am not able to get the description from the product table.
select * from Transactions TR where TR.ListID= '9090909' and TR.ProductID in
(select NDC from Products where Products.ProductID = TR.ProductID)
Any from can help me to get the description too at very few times.
Regards,
View 1 Replies
View Related
Mar 10, 2003
I have a table with one row for each test a user has taken, with columns for userid, score, and test date. I have a query that gets the highest score and the date of the latest test for each user. Easy. But how can I also get the score achieved on the latest test for each agent?
Thanks,
John
(By the way, I've been looking for a good SQL mailing list to ask this question and have been unsuccessful. If there's a better forum than this for this type of question, please let me know).
View 6 Replies
View Related
May 5, 2004
Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
------------
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..
View 3 Replies
View Related
Feb 16, 2006
Godwin writes "Hello,
Heres my question..
I have 2 tables.2 paticular columns exist in both the tables.
I want to be able to select those 2 columns on the 1st table and insert them on to the same 2 columns on the 2nd table.
Now,this 2nd table has another 3 columns that exist in another table.I would like to take those 3 column values from that 3rd table and insert it into the 2nd table by modifying those existing records in the 2nd table.In the 3rd table,there will be around 5 records...I want to copy the existing records 5 times in the 2nd table and insert the 3rd tables rows inside the 2nd table in that respective column for 5 rows.
I hope you understand what I mean...Im sorry for really confusing..
Please help me
Thanks
Godwin"
View 1 Replies
View Related
May 13, 2014
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
View 0 Replies
View Related
Feb 13, 2008
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
Sincerely appreciate all the help I can get.
Thanks in advance.
View 11 Replies
View Related
Jun 25, 2015
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies
View Related
Oct 21, 2013
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
View 8 Replies
View Related
Oct 19, 2007
I have the following table
Name Score Date
A 10 10/19
A 20 10/18
A 30 10/17
B 40 10/18
B 50 10/15
B 60 10/14
I need to get
Name Score Date
A 10 10/19
B 40 10/18
Basically I need one row per Name and that row has to be for the most recent date for
that Name
In Sql 2005 you could do this by
with MyCTE Name,Score,Date,Rownumber as
select Name,Score,Date,Row_Number() (over partition by Name order by Date desc) as Rownnumber
from Table A
Select * from MyCTE where Rownumber=1
Does anyone know how this can be achieved on sql 2000 as Row_number function is not in sql 2000
Thanks for any help in advance
View 3 Replies
View Related
May 9, 2001
Looking for TSQL stored procedure code to parse a single column's value into pieces (each piece stored in a seperate variable) for a subsequent update of other columns on this row -- (generically):
1. Declare/Open cursor on TBLx where COOKIES like '%CLIENT1=' or like '%CLIENT2=%' or like '%CLIENT3=%'
2. Fetch row; initiate 3 Loops to locate the trailing values of CLIENT1=nn, CLIENT2=nn,CLIENT3=nn (eg. CLIENT1=3243, CLIENT2=11, CLIENT3=4451)
3. Save the 3 values in variables (eg @VARclient1=3245, @VARclient2=11, @VARclient3=4451)
4. UPDATE where current of cursor.. SET COL1=@VARclient1, COL2=@VARclient2, col3=@VARclient3)
Caveat.. in step 2 above, the various CLIENT strings can be listed in various orders in any various start positions w/in the COOKIEs column)
(eg. CLIENT3=732,CLIENT1=9,CLIENT2=32 ----- starting in a variable position)
I'm familiar w/ the CHARINDEX feature... Any smaple code would be greatly appreciated.. thx in advance!
BT (william.t.turner@ceridian.com)
View 1 Replies
View Related
Feb 2, 2006
Hi,
I have a table such as
ID Name OS
----------------------------------
10 Paul AIX
10 Paul SOLARIS
10 Paul NT
20 Jack NT
20 Jack SOLARIS
and I have asked to create an output as
ID NAME OS
-----------------------------------
10 Paul AIX,SOLARIS,NT
20 JAck NT,SOLARIS
How can I get this output via sql.
Also a good source for such tricky SQLs would be very fruity.
View 5 Replies
View Related
Oct 25, 2007
hi guys i have a problem;;;
i have to update table'''
querey is--TABLE1 HAS A COLUMN CALLED CITY .THE RECORDS WITH CITY='DELHI' ARE IS TO BE UPDATED WITH CITY='MUMBAI' AND THE RECORDS WITH CITY='MUMBAI' ARE IS TO BE UPDATED WITH CITY='DELHI' ...
i HAVE TO WRITE A SINGLE SQLSTATEMENT TO DO THIS NOT WITH THE HELP OF CURSOR
View 14 Replies
View Related
Jul 23, 2005
Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply
View 14 Replies
View Related
Aug 30, 2007
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.
View 3 Replies
View Related
Nov 19, 2007
I have a situation where I would like to take a single column resultset and convert it to a single comma delimited row within a query. The reason I need this is because the data is from a child table of a parent and my end user has requested to be able to view the data in a tabular fashion. Therefore, I want to add this child table's contents as a comma delimited column in the stored proc I am using to retrieve the data for the display.
View 7 Replies
View Related
Dec 28, 2007
Does some know of an easy to understand way to encrypt a single table column? I have never worked with encryption before.
View 1 Replies
View Related
