The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Equivalent to MS Access' FIRST function?

It's me again. :)

I've been searching for this but can't seem to find anything.  My predecessor left a 76-step process for grabbing and formatting data that we need quarterly (if not sometimes sooner).  Each step (its actually more since there are a few "half" steps) is an individual MS Access query, which I guess he would run in Access first and then transfer the resulting data over to SQL Server.  Anyways I've started some work on converting them over to SQL so I can hopefully write some procedures/functions/etc. and get it to automatically perform.  There is no documentation explaining what each thing does.

Anyways, the first of these queries makes frequent use of the Access FIRST function.  Here's a snippet:

SELECT First(CATEGORY.CAT_ID) AS FirstOfCAT_ID, CATEGORY.CTGRY_NM, SECTION.SECT_ID, SECTION.SECT_NM, SUB_SECTION.SUB_SECT_ID, SUB_SECTION.SUB_SECT_NM, SKU_CATALOG.SKU_GP_ID, First(SKU_GROUP.SKU_GP_DSC) AS SKU_GP_DSC, +[item].[pfx_num]+"-"+[item].[stk_num] AS sku, +[item].[pfx_num]+[item].[stk_num] AS ItemId, First(ITEM.CAT_TXT_HTML) AS CAT_TXT, First(ITEM.STK_NUM) AS STK_NUM, First(ITEM.PFX_NUM) AS PFX_NUM, First(ITEM.ITM_TXT) AS ITM_TXT, First(ITEM.LIST_PR_AMT) AS LIST_PR_AMT, First(SKU_GROUP.ITM_PIC_FIL_NM) AS ITM_PIC_FIL_NM, First(SKU_GROUP.ITM_SPL_PIC_FIL_NM) AS ITM_SPL_PIC_FIL_NM /* Several more */ FROM MANUFACTURER INNER JOIN ((SKU_GROUP INNER JOIN (((CATEGORY INNER JOIN [SECTION] ON (CATEGORY.CTGRY_ID = SECTION.CTGRY_ID) AND (CATEGORY.CAT_ID = SECTION.CAT_ID)) INNER JOIN SUB_SECTION ON (SECTION.SECT_ID = SUB_SECTION.SECT_ID) AND (SECTION.CAT_ID = SUB_SECTION.CAT_ID)) /* several more joins */

Now, I've never really used Access, and I want to ditch it completely if at all possible.  What exactly is First(COLUMN_NAME) supposed to accomplish, and is there a way to effectively mimic it in SQL Server 2005?  I'm not 100% sure what the plethora of queries are doing exactly (I know the general result of what they're doing, but not 100% how they're doing it), so I'm still trying to figure out how to take this steaming pile of shit (which I just found out has several cryptic custom VBA modules doing data manipulation as well as queries) and make it into something optimized and efficient.

Thanks for the help and bearing with my questions.  I'm slowly making progress; management really wants to throw all this garbage out and start over (this guy was really screwing them over; it would take him a week to fix a basic page that displays data from the catalog), but I have to see at least what the current codebase is doing since it (sort of) works.
Newbie IT Director
Wednesday, November 28, 2007
 
 
SELECT TOP 1 <columns you need > FROM <table>
ORDER BY <order by column you need first>
em
Wednesday, November 28, 2007
 
 
Hmm okay, but is there a way to do that for only certain columns?  There seems to be a few queries that use First(COLUMN_NAME) for some specific value, and not for others.  I thought SELECT TOP was all-or-nothing, and you couldn't specify, say: 

SELECT X, TOP 1 Y, Z, AB FROM TBL

Which is how some of the queries appear to be written (e.g. SELECT X, FIRST(Y) AS Something, Z FROM TBL)
Newbie IT Director
Wednesday, November 28, 2007
 
 
76-step process !!!!

Do NOT try to replicate that steaming pile! Figure out the input and output and roll your own.

The First function is a wierd one it is used only with a GROUP BY. It basically means when grouping don't bother checking any other values just return the first one.

Given this data:

ABC Part 1 10.00
ABC Part 1 10.00
ABC Part 1 10.00
ABC Part 2 20.00
ABC Part 2 20.00
EFG Part 4 10.00
EFG Part 5 20.00

SELECT Col1, First(Col2), Sum(Col3)
GROUP BY Col1

Gives you:

ABC Part 1 70.00
EFG Part 4 30.00

I have been programming in Access for 15 years and I have never had a use for it. That the previous dev has used it extensively and created a big massive POS should set off many alarm bells.  I bet the process could be rewritten in a 20 line stored proc.
DJ Send private email
Wednesday, November 28, 2007
 
 
Okay, I'll definitely look at that.  I wasn't planning to recreate it per se, just that it's the only thing to hint at what we need to do.

Maybe I should just speak with the COO since he knows the business more (as he should) and determine what we need to get, and then write my own method.
Newbie IT Director
Wednesday, November 28, 2007
 
 
I've done several re-works of existing "processes" like this and agree that you need both a spec of what is exactly needed from the end user and a list of all of the inputs/transformations/outputs in the current process.

Hopefully you have the data that was used the last time this was done so you can test your new process with it. 

You might find (as I did a couple of times) that there was actually a flaw in the previous process that was never found and your (correct) answer doesn't match what the users were given.  If that happens, be prepared to show (with spreadsheets/etc) exactly why the previous one was wrong - the end users will be absolutely sure that the previous one was right unless you can show them exactly why it was wrong.
RocketJeff Send private email
Wednesday, November 28, 2007
 
 
Yes, we should have the data from the last time; we receive it every quarter and have to load it into our system.  Which gives me about two months time to get it figured out (before the new quarter's files arrive, although the new version of offers the option to get it in XML format instead of Access like the current one).

Pretty much I'm going to try and do it as a SQL Server Integration Service package, so I can do all the manipulations and logic and get it to our format.  Plus with SSIS I can make a package (or several, as the case may be) that will handle the updates to it so we'll have near real-time data.  As I hinted in my post a little below this, the current database's format is hacked together and a lot of it really isn't used or makes much sense.  Management really wants to scrap it and come up with something better (like I said, my predecessor was a self-taught hack who bullshitted them for several years), but until I came aboard they were stuck with the other guy.

I would love for the system to be completely integrated with partners and other vendors (e.g. UPS for real-time tracking), it will just take a while to figure it out and get everything set up.
Newbie IT Director
Wednesday, November 28, 2007
 
 
That may be an attempt to return a single row, when multiple rows apply.  I've seen hacks done like that before at another  job I used to work at using MIN/MAX on SQL Server.
Ted Elliott Send private email
Thursday, November 29, 2007
 
 
Suppose, you've got a table of company staff. Some people change their FamilyNames (wedded/divorced) and you want to get their original FamilyNames. (Yes, it's made up, but no better idea in the morning...) ID is not autonumbering, but staff ID, it doesn't get changed after FamilyName change.

Table staff:
ID, DateFrom, FirstName, FamilyName

SELECT ID, FirstName, FamilyName, DateFrom
FROM staff
QUALIFY row_number() over (partition by ID order DateFrom ASC) = 1

If you don't have ANSI compliant DB, but say SQL Server 2005, you can do:

SELECT ID, FirstName, FamilyName, DateFrom
(
SELECT ID, FirstName, FamilyName, DateFrom, row_number() over row_number() over (partition by ID order DateFrom ASC) as RowID
FROM staff
) staff2
Wojtek
Friday, November 30, 2007
 
 
You seem to have basic problems in system design.  Get some help.  I would guess that an efficient system can be set up to handle your situation without taking forever.
Andrew Bell Send private email
Friday, November 30, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz