Most people use underscores to replace the spaces. It is an informal convention since the beginning of time (or SQL).
Thanks for the help everyone.
Using a CTE is beyond my SQL knowledge so I'll need to study up, you don't think this can be accomplished with a clever WHERE statement or some sort of DISTINCT statement?
There are only two constructs in that query that you may not be familiar with. One is the CTE, and the other is the ROW_NUMBER function. While each of those can have its nuances and complexities, they are very simple the way they have been used here.
You can think of CTE as a sub-query written on top (for purposes of this discussion). So you have subquery, and it can have any name you want - I just chose to name it CTE. Just like in a subquery, that gives you a virtual table and you are selecting from that virtual table. In fact, you can write this query using a subquery if you are more comfortable with that.
The other construct, ROW_NUMBER, simply gives you a row number starting at 1 for each of the rows that are returned in the query. If you have no partition by clause, the numbers will start at 1 and increment for each row returned. If you do have a partition clause, as in this case, the row number will reset for each unique value of the columns in the partition by clause. The order by clause determines which row gets to be number one, and which on number two and so on.
It would be well worth the time to read up on these two constructs - they come in handy in a lot of places in T-SQL. You could use clever programming and avoid these, but they would be harder to read and less efficient.
Thanks James.
I'm just confused as to where to put my existing query.
There appears to be two queries in one, not sure which is the CTE or subquery and what is the main query.
attached is what i think it should be?
;WITH cte AS
(
SELECT
*, -- replace the * with the columns you want to see in your output
ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC) AS RN
FROM
YourTable
)
SELECT
EXISTING QUERY
FROM
EXISTING TABLE
WHERE RN = 1;
It should be the other way around. Take the code below and plug in the followng. If you have an order by clause in the query, remove that and add it back at the very bottom as I have indicated.
------------ FIRST CTE --------------------
;WITH cte1 AS
(
--------- BEGIN: YOUR EXISTING QUERY --------------
SELECT
EXISTING QUERY
FROM
EXISTING TABLE
-- OMIT THE ORDER BY CLAUSE IF YOU HAVE ANY
--------- END: YOUR EXISTING QUERY --------------
),
------------ SECOND CTE --------------------
cte2 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC) AS RN
FROM
cte1
)
------------ FINAL SELECT --------------------
SELECT
*
FROM
cte2
WHERE
RN = 1
ORDER BY
-- YOUR ORDER BY CLAUSE IF ANYHEREThat worked James! Thank you so much for your time and patience.
Ah, yes (and of course I agree) but with the advent of [MyColumnName] allowing any characters, similar to when Filenames started allowing Spaces etc., then teh fact that it is possible to have spaces encourages their use - if we stick to a more narrow naming convention / rule we don't need "[" and "]" at all 
... my recollection is that "[" & "]" came about at the same time that Upsizing Access DBs to SQL Server could all be done with a Wizard, and with that came the spaces in column names - which Access never had a problem with in the first place.
For me, having to put "[" and "]" around a column name, because it contained a space/punctuation character, would be incentive enough not to use spaces!
When we mechanically generate code we only wrap columns with "[" & "]" if the column name contains non alpha-numeric (plus "_") characters, because I find their inclusion peppered in the code at every opportunity!! makes reading the code so much harder (SSMS code-generation is inclined to chuck "[" & "]" around everything "just in case")
e.g.
SELECT @SQLCommand =
CASE WHEN MyColumnName LIKE '%[^A-Z0-9_]%'
THEN QuoteName(MyColumnName)
ELSE MyColumnName
END
etc.
We do something similar when we generate code but augment it to look through a table of SQL keywords and wrap those as well. BOL supplies the original set of keywords in the table.
James:
I just realized there are 3 lines that are returning erroneously because the calculated ship date is incorrect. Is there another PARTITION field I should add to refine the results more? I have SO and SO Line Item.
Thanks.
If ordering by ship date is giving you the wrong row, look at what else you should be ordering by to get the correct row. Why is it wrong? Is it that there is more than one row with the same ship date for a given SO No? If that is the case, look at those three rows and find another column that would make the ordering such that it will give you the row you want. For example, something like this:
ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC, [SO Line Item] ) AS RN
That's what I think I'm doing, I have tried using different fields but this one return in particular keeps returning the wrong ship date, see attached, it's pulling in the first line item 4/18, when it should be returning 4/29 (last line item)
That's posh!
Looking at your screenshot, assuming you are using the column "fshipdate desc" as the order by column and assuming that the partition column is fsokey, it would return the lat row with the date 4/29/2016.
If it is not, it could be because of any number of reasons - for example, if you fshipdate is the wrong data type (it should be DATE, DATETIME, DATETIME2 or one of the other datetime data types), or perhpas there are hidden characters in the fsokey, or something else.
Without seeing your actual data and the exact query, it is hard to say what the cause is.
I think I need to create a select statement to create a calculated ship date column. Something like:
select...
Max(shmast.fshipdate)
FROM SHMAST
WHERE soitem.fsono = SUBSTRING(SHITEM.FSOKEY, 1, 6)
AND (sorels.fenumber = shitem.fenumber)
AND (SOITEM.FSONO + SOITEM.FQUANTITY = SUBSTRING(SHITEM.FSOKEY, 1, 6) + SHITEM.FORDERQTY)) AS MaxOfShipdate,
You can do that, but I don't think there is a need for that. When you post screenshots, it does not help someone to write a query against it. Also, some hidden characters or other features that you may be aware of would not be picked up by someone who looks at a screenshot.
You can run the following query which is a simulation of your data and see what it does. When you post, if you post this type of DDL (table creation script) and sample data, that would be most useful.
CREATE TABLE #shmast(fshipdate DATETIME,fsokey VARCHAR(32));
INSERT INTO #shmast
( fshipdate, fsokey )
VALUES
('20160418','024576 8000'),
('20160425','024576 8000'),
('20160425','024576 8000'),
('20160428','024576 8000'),
('20160428','024576 8000'),
('20160429','024576 8000');
;WITH cte1 AS
(
--------- BEGIN: YOUR EXISTING QUERY --------------
SELECT * FROM #shmast
-- OMIT THE ORDER BY CLAUSE IF YOU HAVE ANY
--------- END: YOUR EXISTING QUERY --------------
),
------------ SECOND CTE --------------------
cte2 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY fsokey ORDER BY fshipdate DESC) AS RN
FROM
cte1
)
SELECT
*
FROM
cte2
WHERE
RN = 1Don't mean to make this more difficult, I appreciate your help immensely!
I can give you my entire query if you'd like, not sure how to post sample data.
BTW, ran what you posted and it worked perfectly, pulled in the correct ship date of 4/29, not 4/18.
Some INSERT statements is usually the best way. Some sample code that created a (e.g. temporary) table, and then some Insert statements, and folk can then try solutions - rather than guessing / hoping if they will work.
To add to what @Kristen said, my last post had sample data that I cooked up. The "CREATE TABLE..." statement created a temporary table. Then, the INSERT statement inserted the data. When you copied and pasted that, you had the table and data against which you can run the query easily.
If you do the same - create table statement and insert statements - then someone can copy that, run it on their system and write a query against that sample data.
okay, another dumb question. how much or what data, the results are obviously pulling from multiple tables.
for example here is the result of current query
sorry if i'm making this more difficult than it is
It is possible to create a query to save the results of your query into a table, and then script that table. However, if you post a large amount of data, that is not going to be very useful to people who are not familiar with your environment or data.
Instead, run your query with additional WHERE clauses to limit the number of rows to just a few that demonstrate the problem. Then, just manually type in the CREATE TABLE statement and INSERT statements, similar to what I had done when I posted my example.